SQL Server From Squirrel’s View
Lockergnome
Home

STB files on SQL Server…

Monday, January 14th, 2008

Today a junior DBA asked me about the files with the .STB extension were for. Then I realized that other DBAs out there may not know what the .STB files are for and what creates them. The .STB files stand for STand By (STB). These files are created when a database is restored with the RESTORE DATABASE command with the clause WITH STAND BY. This statement puts the database in “Stand By Mode” and allows for further backups to be restored.

The caveat here is… when your bring a database online from standby mode the .STB file is sometimes deleted… sometimes it is not. So after you execute the following statement:

RESTORE DATABASE <database name>
WITH RECOVERY

You might still see the file <database name>.stb after the database is brought online. If you still see the file… then you can go ahead and delete the file after the database has been brought online.

Common Bottlenecks In SQL Server

Monday, January 7th, 2008

When your application experiences a performance problem chances are some aspect of your application has or is hitting a bottleneck. What exactly is a bottleneck? Per Wikipedia a bottleneck is a phenomenon where the performance or capacity of an entire system is severely limited by a single component. A common single component in a SQL Server system could be any of the following:

  • CPU
  • Memory
  • I/O
  • tempdb
  • Blocking

There are several tools within SQL Server that you can use to monitor and identify which of these single components are the bottleneck. You can use System Montior, SQL Profiler, sp_who or sp_who2. You can now access the sys.dm (Dynamic Management) procedures and write SQL queries to obtain system information in SQL Server 2005. In my upcoming blog posts I will cover these in more detail so please check back often!

The Different States Of Worker Threads

Friday, January 4th, 2008

Today I was going through one of my production SQL Servers and looking at the connections\spids it dawned on me that some people that are new to SQL Server might not know the different “states” of a worker thread. Here are 3 states that you will come across when executing sp_who or sp_who2:

Running - The worker is currently executing on the CPU

Runnable - The worker is currently waiting for its turn on the CPU

Suspended - The worker is waiting on a resource, for example, a lock or an I/O

These states are good to know in case you need to troubleshoot your system for bottlenecks.

The Value Of A MCDBA

Thursday, December 27th, 2007

Hello and welcome back again…

After a little break I thought of what else people would be interested in and I thought about education. In my years of experience I have not seen an employer “want” or more to the point “request” a person that has an MCDBA. Employers seem to “want” people with “real world” experience as opposed to a “paper” DBA. Now the credential is a great idea by Microsoft and I am sure the certification gives employers the “warm fuzzies”. I can see the MCDBA helping a beginner obtaining a junior level DBA position or a seasoned DBA to getting a promotion.

I am all for education and getting as much as you possibly can. Not only does it benefit the employer indirectly but benefits YOU! I definitely believe the cost of the exams are worth it because it will benefit you in the long run. If you ever go for a job and it is between you and another candidate with the same experience chances are the employer will pick you because of the certification.

Let me know your take or opinion on the MCDBA certification!

Custom Stored Procedure To Restore A Database!

Thursday, December 20th, 2007

Here is a stored procedure that will restore your database without having to remember the SQL syntax of the RESTORE DATABASE command in SQL Server 2000. This stored procedure is to be compiled and run from MSDB. The stored procedure reads from the registry to figure out the data and log path of the database files. To execute this stored procedure run the following statement:

exec pr_restore_database_new ‘ database name’, ‘the path of the backup file’, ‘backup type’

The backup types are: B = Full Backup, D = Differential Backup, L = Log Backup

The caveat here is that the way the stored procedure is written the database backup needs to be in its own backup folder. So for example, D:\DBBackups\Test\Test.bak

Here is the script:

CREATE procedure pr_restore_database_new(
@bk_name nvarchar(50),
@bk_path nvarchar(120),
@bk_type char(1)
)

as

set nocount on
declare @bk_file_name nvarchar(128)
declare @sql nvarchar(1000)
declare @file int
declare @data_path nvarchar(128)
declare @log_path nvarchar(128)

declare @last_lsn numeric(25,0)

set @file = 1
set @bk_file_name = @bk_name

exec master.dbo.xp_instance_regread N‘HKEY_LOCAL_MACHINE’,N‘Software\Microsoft\MSSQLServer\MSSQLServer’,N‘DefaultData’, @data_path output
exec master.dbo.xp_instance_regread N‘HKEY_LOCAL_MACHINE’,N‘Software\Microsoft\MSSQLServer\MSSQLServer’,N‘DefaultLog’, @log_path output

set @bk_path = @bk_path + @bk_name + ‘\’

Create Table #HeaderOnly(
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed tinyint,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreateionDate datetime,
BackupSize numeric(20, 0),
FirstLSN numeric(25, 0),
LastLSN numeric(25, 0),
CheckpointLSN numeric(25, 0),
DatabaseBackupLSN numeric(25, 0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
CodePage smallint,
UnicodeLocaleId int,
UnicodeComarisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajory int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingID uniqueidentifier,
RecoverForkID uniqueidentifier,
Collation nvarchar(128)
)

Create Table #FileListOnly(
LogicalName nvarchar(128),
PhysicalName nvarchar(128),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0)
)

if(@bk_type = ‘B’)
Begin

Declare @LogicalName nvarchar(128)
Declare @PhysicalName nvarchar(128)
Declare @Type char(1)

–*************************** Get The Logical Names from the Backup ****************************

set @bk_file_name = @bk_file_name + ‘_full’
set @sql = ‘Restore FileListOnly From disk = ”’ + @bk_path + @bk_file_name + ‘.bak”’

insert into #FileListOnly
execute sp_executesql @sql

set @sql = ‘Restore Database ‘ + @bk_name + char(13)
set @sql = @sql + ‘From Disk = ”’ + @bk_path + @bk_file_name + ‘.bak”’ + char(13)
set @sql = @sql + ‘With’ + char(13)

Declare c1 cursor for
select LogicalName, reverse((left(reverse(PhysicalName), charindex(’\', reverse(PhysicalName)) - 1))) as PhysicalName, Type
from #FileListOnly
order by Type

open c1

fetch next from c1
into @LogicalName, @PhysicalName, @Type

while @@fetch_status = 0
begin
if(@Type = ‘D’)
begin
set @sql = @sql + char(9) + ‘Move ”’ + @logicalName + ”’ to ”’ + @data_path + @PhysicalName + ”’,’ + char(13)
end
else
begin
set
@sql = @sql + char(9) + ‘Move ”’ + @logicalName + ”’ to ”’+ @log_path + @PhysicalName + ”’,’ + char(13)
end

fetch next from c1
into @LogicalName, @PhysicalName, @Type
end
close
c1
deallocate c1

set @sql = @sql + char(9) + ‘Standby = ”’ + @data_path + @bk_name + ‘.stb”’

–print @sql
execute sp_executesql @sql
End
else if
(@bk_type = ‘D’)
begin

set @bk_file_name = @bk_file_name + ‘_’ + cast(datepart(dw, getdate()) as varchar)
set @sql = ‘Restore HeaderOnly From disk = ”’ + @bk_path + @bk_file_name + ‘_Diff.bak”’

insert into #HeaderOnly
execute sp_executesql @sql

set @file = (select max(position) from #HeaderOnly)

truncate table #HeaderOnly

if(@file is not null)
begin
set
@sql = ‘Restore Database ‘ + @bk_name + char(13)
set @sql = @sql + ‘From Disk = ”’ + @bk_path + @bk_file_name + ‘_Diff.bak”’ + char(13)
set @sql = @sql + ‘With’ + char(13)
set @sql = @sql + char(9) + ‘File = ‘ + cast(@file as varchar) + ‘, ‘ + char(13)
set @sql = @sql + char(9) + ‘Standby = ”’ + @data_path + @bk_name + ‘.stb”’

–print @sql
execute sp_executesql @sql
end
end
else if
(@bk_type = ‘L’)
begin
while
(@file is not null)
begin

set @sql = ‘Restore HeaderOnly From disk = ”’ + @bk_path + @bk_name + ‘_Log.bak”’

set @last_lsn = (select last_lsn from backupset
where backup_set_id = (select backup_set_id
from restorehistory
where restore_history_id = (select max(restore_history_id)
from restorehistory
where destination_database_name = @bk_name)))

insert into #HeaderOnly
execute sp_executesql @sql

set @file = (select position
from #HeaderOnly
where(FirstLsn <= @last_lsn and LastLsn > @last_lsn))

truncate table #HeaderOnly

if(@file is not null)
begin
set
@sql = ‘Restore Log ‘ + @bk_name + char(13)
set @sql = @sql + ‘From Disk = ”’ + @bk_path + @bk_name + ‘_Log.bak”’ + char(13)
set @sql = @sql + ‘With’ + char(13)
set @sql = @sql + char(9) + ‘File = ‘ + cast(@file as varchar) + ‘, ‘ + char(13)
set @sql = @sql + char(9) + ‘Standby = ”’ + @data_path + @bk_name + ‘.stb”’

– print @sql
execute sp_executesql @sql
end
end
end
else if
(@bk_type = ‘R’)
begin
set
@sql = ‘Restore Database ‘ + @bk_name + char(13)
set @sql = @sql + ‘With’ + char(13)
set @sql = @sql + char(9) + ‘Recovery’

print @sql
–execute sp_execute @sql
end

Drop Table #FileListOnly
Drop Table #HeaderOnly

GO

SSIS In A Cluster

Wednesday, December 19th, 2007

Today I wanted to share with you my “painful” experience with having SQL Server Integration Services (SSIS) installed on a cluster. I just got done installing and configuring a 4 node (3 active 1 passive) cluster. It has Windows 2003 and SQL Server 2005 Enterprise edition installed on it. I got the database engine, reporting services, analysis services and integration services installed and configured properly.

After installing and configuring everything we did a fail-over test. Everything had failed over except for SSIS. Come to find out that SSIS IS NOT cluster aware. You can view the article here that I came across on Microsoft’s website. There are work arounds or “hacks” as I like to call them. Because I am supporting a large production environment and need to minimize downtime as much as possible, we created a separate stand-alone SSIS server. From the SQL Server agent jobs on the production SQL Servers we do RPCs to the SSIS server that kicks off the packages. This has worked great! If anyone has succeeded in the “work arounds” or the “hacks” let us know!

Watch the latest videos on YouTube.com

How To Parse A Text File With VBScript!

Tuesday, December 18th, 2007

I thought today we would switch gears from SQL Server and T-SQL and do something a little more exciting. Today I am going to introduce you to VBScript and what it can do for a DBA. Now some of you may already know VBScript and some of you may not which is ok. I have found VBScript useful in splitting text files, creating text files, interacting with files on the OS level, etc. Today’s example will be splitting a text file into 2 separate files.

You can write VBScript in any type of text editor and save the file with a .vbs extension. I have downloaded VbsEdit from here. The interface is straight forward and it is easy to use. Below is the script that parses one main work file:

‘ ****************************************
‘ **
‘ ** WORK FILE SPLITTER
‘ ** Brett Davis
‘ ** 2007.11.01
‘ **
‘ ****************************************

‘ Declare variables.
Dim fileDate
Dim objFSO
Dim objFile
Dim objFileA
Dim objFileD
Dim strDate
Dim strLine
Dim strCharacter

Set objFSO = CreateObject(”Scripting.FileSystemObject”)

‘ Get Work File.
Set objFile = objFSO.OpenTextFile(”C:\WORK\WORKFILE.WRK”, 1)

‘ Verify if first work file WORKFILEA.txt exists.
If objFSO.FileExists(”C:\WORK\WORKFILEA.txt”) Then
Set objFileA = objFSO.GetFile(”C:\WORK\WORKFILEA.txt”)
fileDate = objFileA.DateLastModified

strDate = DatePart(”yyyy”, fileDate) & Right(”0″ & _
DatePart(”m”, fileDate), 2) & Right(”0″ & DatePart(”d”, fileDate), 2)

‘ Append date to file if it already exists.
objFileA.Name = “WORKFILEA_” & strDate & “.txt”

Set objFileA = objFSO.CreateTextFile(”C:\WORK\WORKFILEA.txt”,1)
Else
Set
objFileA = objFSO.CreateTextFile(”C:\WORK\WORKFILEA.txt”,1)
End If

‘Verify if second work file WORKFILED.txt exists.
If objFSO.FileExists(”C:\WORK\WORKFILED.txt”) Then
Set objFileD = objFSO.GetFile(”C:\WORK\WORKFILED.txt”)
fileDate = objFileD.DateLastModified

strDate = DatePart(”yyyy”, fileDate) & Right(”0″ & _
DatePart(”m”, fileDate), 2) & Right(”0″ & DatePart(”d”, fileDate), 2)

‘ Append date to file if it already exists.
objFileD.Name = “WORKFILED_” & strDate & “.txt”

Set objFileD = objFSO.CreateTextFile(”C:\WORK\WORKFILED.txt”,1)
Else
Set
objFileD = objFSO.CreateTextFile(”C:\WORK\WORKFILED.txt”,1)
End If

‘ Start iterating through the main file and begin split. Look for the first character to be either an ‘A’ or a ‘D’.
Do Until objFile.AtEndOfStream
strCharacter = objFile.Read(1)

If strCharacter = “A” Then
strLine = strCharacter + objFile.ReadLine
‘WScript.Echo strLine
objFileA.WriteLine strLine
End If

If strCharacter = “D” Then
strLine = strCharacter + objFile.ReadLine
‘WScript.Echo strLine
objFileD.WriteLine strLine
End If
Loop

‘ Destroy objects.
objFile.Close
objFileA.Close
objFileD.Close

MsgBox “Files have been processed.”

Victim Of Parameter Sniffing?

Friday, December 14th, 2007

Have you come across a time when a stored procedure takes a lot longer to run than normal but the T-SQL itself runs a lot faster in Query Analyzer? This might be due to “parameter sniffing”. During compilation of a stored procedure the query optimizer attempts to “sniff” or “seek out” the values being passed in… in order to create an optimized query plan. The problem occurs when values that are passed in does not match a plan in the procedure cache and SQL Server attempts to create a new plan in the cache. This will cause the procedure to take much longer to execute and performance decreases.

The “fix” for parameter sniffing is to create local variables within the stored procedure that match the parameters being passed in. So for example,

Create Procedure myProc @name varchar(30), @age int

As

Declare @name_local varchar(30), @age_local int

Set @name_local = @name

Set @age_local = @age

Then use @name_local and @age_local throughout your stored procedure and you will get the same execution times as if you were running the straight T-SQL in query analyzer. Let me know if this helps!

Implementing A Disaster Recovery System

Thursday, December 13th, 2007

Today I wanted to share the different ways of maintaining a Disaster Recovery (DR) system. One of the most important functions that a Database Administrator has… is to design and implement a DR plan for a production environment. Planning for a disaster has the following aspects:
1. Creating a backup plan for all databases
2. Setting up a “warm” backup server with the databases that are critical
3. Testing the backups at regular intervals
4. Circulating backups for off-site storage
5. Documenting the backup recovery system and to train co-workers in DR
procedures

Here are the different methods for maintaining a DR system:
1. SQL Server Cluster (SQL Server 2000 and 2005)
2. Warm Standby Server (SQL Server 2000 and 2005)
3. This can be maintained by log shipping, RPCs (job based), Transaction
Replication or through DTS or SSIS.

DR enhancements in SQL Server 2005
1. Database Mirroring
2. Peer To Peer Replication

I suggest to read up on each of these methods and decide which DR system fits your back-end infrastructure.

Scripting SQL Server 2005 Logins

Thursday, December 6th, 2007

I thought today I would share with you a great way to move SQL Server logins from one SQL Server 2005 server (source) to another SQL Server 2005 server (destination).  In the past I have tried to use DTS to move logins (passwords and SIDs) from one server to another but without much success.  As I was surfing the Internet, I came across 2 stored procedures from Microsoft that you create in the master database.  Once compiled, run the primary stored procedure and the result set produces the CREATE LOGIN statements with the correct passwords and SIDs for every login in the source server.  Once this script has been created, you can then copy it and execute the script in a query window on the destination server.  Once you restore the user databases from the source server to the destination server the SIDs found in the user database will then be sync’d to the logins in the master database. 

You will find both stored procedures below in one script.  The primary stored procedure you execute to generate the CREATE LOGIN script is the stored procedure:  sp_help_revlogin   Here is the correct syntax to run the procedure for ALL logins.  exec sp_help_revlogin

Here is the script with the 2 stored procedures:

USE master
GO

IF OBJECT_ID (’sp_hexadecimal’) IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO

CREATE PROCEDURE sp_hexadecimal
@binvalue VARBINARY(256),
@hexvalue VARCHAR(514) OUTPUT

AS

/*
**********************************************************************
**
** Object Name: sp_hexadecimal
** Object Type: Stored Procedure
** File Name: sp_hexadecimal.sql
** Database Name: master
** Author: < Name of the person who wrote the T-SQL
** script >
** Creation Date: 07.10.08 - use ANSI format
** Description: This procedure is involved in scripting out
** logins, passwords and SIDs in SQL Server 2005.
** Special Note: This script is also available from the
** Microsoft website.
** Modified Tables: None
** Called Procedures: None
** Called Jobs: None
** Called UDFs: None
** Calling Procedures: sp_help_revlogin
** Calling Jobs: None
** Calling UDFs: None
** Return Statuses: @hexvalue as OUTPUT
**
**********************************************************************
*/

– Declaration section
DECLARE @charvalue VARCHAR(514)
DECLARE @i INT
DECLARE @length INT
DECLARE @hexstring CHAR(16)

– Initialization section
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH(@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’

WHILE (@i <= @length)
BEGIN
– Declaration section
DECLARE @tempint INT
DECLARE @firstint INT
DECLARE @secondint INT

– Initialization section
SELECT @tempint = CONVERT(INT, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +

SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1)

SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

/******************************************************************************/
IF OBJECT_ID (’sp_help_revlogin’) IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO

CREATE PROCEDURE sp_help_revlogin
@login_name SYSNAME = NULL

AS

/*
**********************************************************************
**
** Object Name: sp_help_revlogin
** Object Type: Stored Procedure
** File Name: sp_help_revlogin.sql
** Database Name: master
** Author: < Name of the person who wrote the T-SQL
** script >
** Creation Date: 07.10.08 - use ANSI format
** Description: This procedure creates the actual T-SQL ‘Create
** Login’ statement with the information from
** the security tables in SQL Server 2005.
** Special Note: This script is also available from the
** Microsoft website.
** Modified Tables: None
** Called Procedures: sp_hexadecimal
** Called Jobs: None
** Called UDFs: None
** Calling Procedures: None
** Calling Jobs: None
** Calling UDFs: None
** Return Statuses: None
**
**********************************************************************
*/

– Declaration section
DECLARE @name SYSNAME
DECLARE @type VARCHAR(1)
DECLARE @hasaccess INT
DECLARE @denylogin INT
DECLARE @is_disabled INT
DECLARE @PWD_varbinary VARBINARY(256)
DECLARE @PWD_string VARCHAR(514)
DECLARE @SID_varbinary VARBINARY(85)
DECLARE @SID_string VARCHAR(514)
DECLARE @tmpstr VARCHAR(1024)
DECLARE @is_policy_checked VARCHAR(3)
DECLARE @is_expiration_checked VARCHAR(3)

IF @login_name IS NULL
DECLARE login_curs CURSOR FOR
SELECT
p.sid,
p.[name],
p.[type],
p.is_disabled,
l.hasaccess,
l.denylogin
FROM sys.server_principals AS p
LEFT JOIN sys.syslogins AS l
ON l.[name] = p.[name]
WHERE p.[type] IN( ‘S’, ‘G’, ‘U’ )
AND p.[name] <> ’sa’
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid,
p.[name],
p.[type],
p.is_disabled,
l.hasaccess,
l.denylogin
FROM sys.server_principals AS p
LEFT JOIN sys.syslogins AS l
ON l.[name] = p.[name]
WHERE p.[type] IN( ‘S’, ‘G’, ‘U’ )
AND p.[name] = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs
INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin

IF (@@FETCH_STATUS= -1)
BEGIN
PRINT ‘No login(s) found.’
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END

SET @tmpstr = ‘/* sp_help_revlogin script ‘

PRINT @tmpstr

SET @tmpstr = ‘** Generated ‘ + CONVERT (VARCHAR, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’

PRINT @tmpstr
PRINT

WHILE ( @@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
PRINT
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstr

IF (@type IN ( ‘G’, ‘U’))
BEGIN – NT authenticated account/group
SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘FROM WINDOWS’
END
ELSE
BEGIN
– SQL Server authentication
– obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS VARBINARY(256))
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

– obtain password policy state
SELECT @is_policy_checked =
CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END
FROM
sys.sql_logins
WHERE [name] = @name

SELECT @is_expiration_checked =
CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END
FROM
sys.sql_logins
WHERE [name] = @name

SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name )
+ ‘ WITH PASSWORD = ‘ + @PWD_string
+ ‘ HASHED, SID = ‘ + @SID_string

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET
@tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
END

IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
END
END

IF (@denylogin = 1)
BEGIN – login is denied access
SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
END
ELSE IF
(@hasaccess = 0)
BEGIN – login has exists but does not have access
SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
END

IF (@is_disabled = 1)
BEGIN — login is disabled
SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
END

PRINT @tmpstr
END

FETCH NEXT FROM login_curs
INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin

END

CLOSE login_curs
DEALLOCATE login_curs

RETURN 0
GO