Microsoft KB Archive/122485

{|
 * width="100%"|

INF: Identifying SPID Responsible for Lock Chain

 * }

Q122485

-

The information in this article applies to:


 * Microsoft SQL Server version 4.2x

-

SUMMARY
Processes may block on locks held by another process, which in turn, is blocked by another process. If many processes are involved in a long chain of locks, it may be difficult to determine which process is ultimately responsible for the blocking by tracing through the sp_who output.

MORE INFORMATION
The following stored procedure can be used to determine which process is ultimately responsible for blocking a given process:

  create proc chaintrace @spid smallint as  declare @current_spid smallint declare @blocker_spid smallint select @current_spid = @spid select @blocker_spid = blocked from master.dbo.sysprocesses where spid = @current_spid while @blocker_spid != 0 begin select @current_spid = @blocker_spid select @blocker_spid = blocked from master.dbo.sysprocesses where spid = @current_spid end select "process: "+convert( char, @current_spid )+ " at root of lock  chain" Additional query words: Windows NT Transact-SQL locking blocked

Keywords : kbother

Issue type : kbinfo

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2