|
scottpt
Posting Yak Master
USA
186 Posts |
 Posted - 01/29/2004 : 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 |
|
Show Lead Blockers
