Skip to content
 

Blocking Processes (lead blocker)

Finding the lead blocker in a chain of blocks is a difficult challenge when using sp_who2. There are also a lot of detect blocking scripts that I have run in production environments that seem to block things themselves.

Probably the most efficient way to detect blocks is to query sysprocesses. The following script displays the lead blocker in a chain of blocks:

SELECT
	spid
	,status
	,loginame	= SUBSTRING(loginame, 1, 12)
	,hostname	= SUBSTRING(hostname, 1, 12)
	,blk		= CONVERT(char(3), blocked)
	,open_tran
	,dbname		= SUBSTRING(DB_NAME(dbid),1,10)
	,cmd
	,waittype
	,waittime
	,last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
Share and Enjoy:
  • Print this article!
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks

Related Posts:

  • » Kick Users Off Database
  • » Rebuild and Reorganize Fragmented Indexes
  • » Using UPDATE STATISTICS for All Tables
  • » Troubleshooting SQL Server Slowness
  • » Performance Counters
    blog comments powered by Disqus