Capture blocking details in SQL 2005 along with SQL Commands for both SPID and Blocked

If you want to store all blocking details in your databases in SQL Server 2005 then you can use the below method.

Step 1 Run the below script to create a table

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_PADDINGOFF

GO

CREATETABLE [dbo].[MyBlockingInfo](

[CaptureDateTime] [datetime] NOTNULL,

[spid] [smallint] NOTNULL,

[kpid] [smallint] NOTNULL,

[blocked] [smallint] NOTNULL,

[waittype] [binary](2)NOTNULL,

[waittime] [bigint] NOTNULL,

[lastwaittype] [nchar](32)NOTNULL,

[waitresource] [nchar](256)NOTNULL,

[dbid] [smallint] NOTNULL,

[uid] [smallint] NULL,

[cpu] [int] NOTNULL,

[physical_io] [bigint] NOTNULL,

[memusage] [int] NOTNULL,

[login_time] [datetime] NOTNULL,

[last_batch] [datetime] NOTNULL,

[ecid] [smallint] NOTNULL,

[open_tran] [smallint] NOTNULL,

[status] [nchar](30)NOTNULL,

[sid] [binary](86)NOTNULL,

[hostname] [nchar](128)NOTNULL,

[program_name] [nchar](128)NOTNULL,

[hostprocess] [nchar](10)NOTNULL,

[cmd] [nchar](16)NOTNULL,

[nt_domain] [nchar](128)NOTNULL,

[nt_username] [nchar](128)NOTNULL,

[net_address] [nchar](12)NOTNULL,

[net_library] [nchar](12)NOTNULL,

[loginame] [nchar](128)NOTNULL,

[context_info] [binary](128)NOTNULL,

[sql_handle] [binary](20)NOTNULL,

[stmt_start] [int] NOTNULL,

[stmt_end] [int] NOTNULL,

[request_id] [int] NOTNULL

)ON [PRIMARY]

GO

SETANSI_PADDINGOFF

Step 2 Run the below script to create a table to capture SQL Command

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATETABLE [dbo].[CAPTUREINPUTBUFFERVALUE](

[CPDATETIME] [datetime] NULL,

[SPIDTXT] [nvarchar](4000)NULL,

[BKSPIDTXT] [nvarchar](4000)NULL

)ON [PRIMARY]

Step 3 Run the below script to create trigger on table MyBlocking

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATETRIGGER [dbo].[CAPTUREINPUTBUFFER]

ON [dbo].[MyBlocking]

FORINSERT

AS

begin

DECLARE @SQLCOMMAND VARCHAR(100)

DECLARE @SQLCOMMANDbk VARCHAR(100)

DECLARE @CPDATE DATETIME

DECLARE @SPID INT

DECLARE @BKSPID INT

DECLARE @SPIDTXT  NVARCHAR(4000)

DECLARE @BKSPIDTXT NVARCHAR(4000)

DECLARE @SQLHandle BINARY(20)

DECLARE @SQLHandleBK BINARY(20)

set @CPDATE=(select CAPTUREDATETIME from INSERTED wheredbid=13)

set @SPID=(select SPID from INSERTED wheredbid=13)

set @BKSPID=(select BLOCKED from INSERTED wheredbid=13)

— this will give you the SQLHandle for the culprit SPID

SELECT @SQLHandle =SQL_HANDLEFROM MASTER..SYSPROCESSES WHERE SPID = @SPID

SELECT @SQLHandleBK =SQL_HANDLEFROM MASTER..SYSPROCESSES WHERE SPID = @BKSPID

— this statement will give you the SQL Statement for culprit SPID

SET @SPIDTXT=(SELECT [TEXT] FROM::FN_GET_SQL(@SQLHandle))

SET @BKSPIDTXT=(SELECT [TEXT] FROM::FN_GET_SQL(@SQLHandleBK))

IF @SPID ISNOTNULL

INSERTINTO CAPTUREINPUTBUFFERVALUE VALUES(@CPDATE,@SPIDTXT,@BKSPIDTXT)

END

Step 4 Run the below script to create a table

Create 2 jobs with an interval of 30 seconds inserting sysprocesses information in MyBlocking table.

@command=N’Insert into dbo.MyBlocking

select getdate(),* from sys.sysprocesses where blocked <>0′,

When the above jobs execute they will insert the blocking information in your blocking table MyBlocking and then the trigger will capture the details of running process and the blocking process in another table for future references and output will be shown as below

deadlock

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s