Microsoft KB Archive/283725

= INF: How to View SQL Server 2000 Blocking Data =

Article ID: 283725

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q283725



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)

Keywords: kbinfo KB283725

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.