syslocks

[Simon]

Is there a way to determine how many processes are directly or indirectly
blocked by a process?

I know the following query will give all processes which are blocking others but not blocked themselves:

select * from master..sysprocesses
where blocked = 0
and spid in (select blocked from master..sysprocesses where blocked>0)

However I would like to go further and determine how many processes are held up by the one process.

Is this possible in a query rather than a stored procedure?

[Patrick]

Try this SQL ( it is for 7.X ) run this in master though

SET NOCount on
exec sp_blockcnt
SELECT GETDATE(), @@servername + CHAR(10)

declare @blocker_spid smallint
declare @i_buff_string char(30)
set nocount on



select spid, blocked, hostname=substring (hostname, 1, 10),
progname=substring(program_name, 1, 10), cmd=substring(cmd, 1, 10),
status, physical_io, waittype
into #blk from master..sysprocesses (nolock) where blocked != 0



delete from #blk
where blocked in (select spid from #blk)



select "Blocking spid" = spid, loginame=substring(suser_name(suid),1,10),
hostname=substring (hostname, 1, 10), progname=substring(program_name, 1,
10),
cmd=substring(cmd, 1, 10), status, physical_io, waittype
from master..sysprocesses (nolock)
where spid in
(select blocked from #blk)
-- get root spid

declare blk_cursor CURSOR FOR SELECT blocked from #blk
open blk_cursor
fetch next from blk_cursor into @blocker_spid
while (@@fetch_status <> -1)
begin
select @i_buff_string = (&#34;dbcc inputbuffer (&#34; +
convert(char(6),@blocker_spid) +&#34&#34
select &#34;Below is input buffer for this blocking spid: &#34;, @blocker_spid
select &#34;&#34;
exec (@i_buff_string)
fetch next from blk_cursor into @blocker_spid
end
deallocate blk_cursor



select spid, syslocks.type, locktype=name, table_id=id, page, dbid
from syslocks, master.dbo.spt_values v
where syslocks.type=v.number
and v.type=&#39;L&#39;
and (syslocks.type & 256)=256
and spid in (select blocked from #blk)
order by spid

drop table #blk
select &#34;Total users &#34;,count(*) from sysprocesses (nolock)
select &#34;Blocked users &#34;,count(*) from sysprocesses (nolock) where blocked <> 0
print &#34;&#34;

/* Get blocked spids */
Print &#34;Get blocked spids...&#34;
go
select a.spid,
a.status,
a.hostname,
a.blocked &#34;blocking spid&#34;,
a.login_time,
a.last_batch
from sysprocesses a (nolock)
where a.blocked <> 0
print &#34;&#34;
go



------------
Simon at 10/28/99 4:18:33 AM

Is there a way to determine how many processes are directly or indirectly
blocked by a process?

I know the following query will give all processes which are blocking others but not blocked themselves:

select * from master..sysprocesses
where blocked = 0
and spid in (select blocked from master..sysprocesses where blocked>0)

However I would like to go further and determine how many processes are held up by the one process.

Is this possible in a query rather than a stored procedure?

[tiger]

If you are using SQL 7, another way to check out the blocking is using Enterprise Manager. Goto Management->Current Activity->Locks/Process ID. You will see who is blocker and who are being blocked.



------------
Simon at 10/28/99 4:18:33 AM

Is there a way to determine how many processes are directly or indirectly
blocked by a process?

I know the following query will give all processes which are blocking others but not blocked themselves:

select * from master..sysprocesses
where blocked = 0
and spid in (select blocked from master..sysprocesses where blocked>0)

However I would like to go further and determine how many processes are held up by the one process.

Is this possible in a query rather than a stored procedure?