Show Lead Blockers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Show Lead Blockers

Author

Topic

scottpt
Posting Yak Master

186 Posts

Posted - 2004-01-29 : 13:08:51

/*******************************************************************************
**
** This script will show you the lead blockers and all blocked processes.
**
**
*********************************************************************************/

set nocount on
SELECT spid,blocked,sd.name 'database',sl.name 'login',hostname,program_name
INTO #blocks
FROM master..sysprocesses sp
inner join sysxlogins sl
on sp.sid=sl.sid
inner join sysdatabases sd
on sp.dbid=sd.dbid
WHERE blocked <> 0

Print 'Lead Blockers'
print '============================================================================================='
SELECT sp.spid,sp.blocked,sd.name 'database',sl.name 'login',sp.hostname,sp.program_name
FROM master..sysprocesses sp
inner join sysxlogins sl
on sp.sid=sl.sid
inner join sysdatabases sd
on sp.dbid=sd.dbid
inner join #blocks b
on b.blocked=sp.spid
and sp.blocked=0

Print 'Blocked'
print '============================================================================================='
Select * from #blocks

drop table #blocks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources