INF: How to View SQL Server 2000 Blocking Data (283725)

SUMMARY

This article defines views that can be used to view blocking data contained in a trace file that is created by the job described in the following article in the Microsoft Knowledge Base:

283696 INF: Job to Monitor SQL Server 2000 Performance and Activity

Before you create these views, the trace file must be imported into a SQL Server 2000 table named ActivityTrace. The script for a stored procedure called trace_gettable, which imports the data from the trace file to a SQL Server 2000 table, is defined in the following article:

283784 INF: How to View SQL Server 2000 Activity Data

Use the views that are defined below to display blocking data as noted:

The v_blocked view lists the system process IDs (SPIDs) that are blocked.
The v_blocking view lists all of the SPIDs involved in blocking.
The v_blockspids1 view is a summary list of SPIDs that are blocking other spids. The RowNumber column in this view is the row number from the ActivityTrace table and trace file. Use the following query to view the TextData column from the ActivityTrace table for rows prior to the row listed in the v_blockspid1 view.
```
select TextData from ActivityTrace where RowNumber > 4080 and RowNumber < 5000
						
```
Change the second number (5000) in the above query to the value for RowNumber returned in v_blockspid1. Change the first number (4080) to reflect the number of previous rows you wish to view. For example, if v_blockspid1 returns RowNumber 52365 and you wish to view the 20 rows from the ActivityTrace table prior to that row, issue the following statement:
```
select TextData from ActivityTrace where RowNumber > 52345 and RowNumber < 52365
					
```
The v_blockspids2 view is a detailed list of SPIDs that are blocking other SPIDs.

MORE INFORMATION

CREATE    view v_blocked as 
select RowNumber,StartTime
      ,TextData
      ,cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,cast(substring(BinaryData,3,2) as smallint) as [kpid]
      ,cast(substring(BinaryData,5,2) as smallint) as [blocked]
      ,cast(substring(BinaryData,7,2) as binary(2)) as [waittype]
      ,cast(substring(BinaryData,9,4) as int) as [waittime]
      ,cast(substring(BinaryData,13,64) as nchar(32)) as [lastwaittype]
      ,cast(substring(BinaryData,77,512) as nchar(256)) as [waitresource]
      ,cast(substring(BinaryData,589,2) as smallint) as [dbid]
      ,cast(substring(BinaryData,591,2) as smallint) as [uid]
      ,cast(substring(BinaryData,593,4) as int) as [cpu]
      ,cast(substring(BinaryData,597,8) as bigint) as [physical_io]
      ,cast(substring(BinaryData,605,4) as int) as [memusage]
      ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
      ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
      ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
      ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
      ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
      ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
      ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
      ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
      ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
      ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
      ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
      ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
      ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
      ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
      ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
      ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
   and cast(substring(BinaryData,5,2) as smallint) != 0

CREATE   view v_blocking as
select RowNumber,StartTime
      ,TextData
      ,cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,cast(substring(BinaryData,3,2) as smallint) as [kpid]
      ,cast(substring(BinaryData,5,2) as smallint) as [blocked]
      ,cast(substring(BinaryData,7,2) as binary(2)) as [waittype]
      ,cast(substring(BinaryData,9,4) as int) as [waittime]
      ,cast(substring(BinaryData,13,64) as nchar(32)) as [lastwaittype]
      ,cast(substring(BinaryData,77,512) as nchar(256)) as [waitresource]
      ,cast(substring(BinaryData,589,2) as smallint) as [dbid]
      ,cast(substring(BinaryData,591,2) as smallint) as [uid]
      ,cast(substring(BinaryData,593,4) as int) as [cpu]
      ,cast(substring(BinaryData,597,8) as bigint) as [physical_io]
      ,cast(substring(BinaryData,605,4) as int) as [memusage]
      ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
      ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
      ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
      ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
      ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
      ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
      ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
      ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
      ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
      ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
      ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
      ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
      ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
      ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
      ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
      ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'

create view v_blockspids1 as
select top 100 percent
       cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,min(RowNumber) as RowNumber
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
   and cast(substring(BinaryData,5,2) as smallint) = 0
   and cast(substring(BinaryData,1,2) as smallint) in 
     (select cast(substring(BinaryData,5,2) as smallint) as spid
        from ActivityTrace
       where cast(substring(BinaryData,5,2) as smallint) != 0)
 group by cast(substring(BinaryData,1,2) as smallint)
 order by cast(substring(BinaryData,1,2) as smallint)

CREATE    view v_blockspids2 as 
select RowNumber,StartTime
      ,TextData
      ,cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,cast(substring(BinaryData,3,2) as smallint) as [kpid]
      ,cast(substring(BinaryData,5,2) as smallint) as [blocked]
      ,cast(substring(BinaryData,7,2) as binary(2)) as [waittype]
      ,cast(substring(BinaryData,9,4) as int) as [waittime]
      ,cast(substring(BinaryData,13,64) as nchar(32)) as [lastwaittype]
      ,cast(substring(BinaryData,77,512) as nchar(256)) as [waitresource]
      ,cast(substring(BinaryData,589,2) as smallint) as [dbid]
      ,cast(substring(BinaryData,591,2) as smallint) as [uid]
      ,cast(substring(BinaryData,593,4) as int) as [cpu]
      ,cast(substring(BinaryData,597,8) as bigint) as [physical_io]
      ,cast(substring(BinaryData,605,4) as int) as [memusage]
      ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
      ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
      ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
      ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
      ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
      ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
      ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
      ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
      ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
      ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
      ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
      ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
      ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
      ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
      ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
      ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
   and cast(substring(BinaryData,5,2) as smallint) = 0
   and cast(substring(BinaryData,1,2) as smallint) in 
     (select cast(substring(BinaryData,5,2) as smallint) as spid
        from ActivityTrace
       where cast(substring(BinaryData,5,2) as smallint) != 0)