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