Blocked Transactions

I was wanting to write a stored procedure which would identify queries which
are currently being blocked (spids 'blocked by' some other spid) when the
stored procedure is being run.
Is this possible? How do I go about figuring out how to do this?This query will get you the head of the blocking chain...
Declare @.SPID Varchar(500)
Select @.SPID = COALESCE(@.SPID + ',', '') + Cast(a.spid as varchar(20))
From master.dbo.sysprocesses a
where a.blocked = 0 and
a.spid IN (Select b.blocked From master.dbo.sysprocesses b Where b.blocked
!= 0)
Select @.SPID
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Jim Heavey" <JimHeavey@.hotmail.com> wrote in message
news:O$u7BzDmDHA.2080@.TK2MSFTNGP10.phx.gbl...
> I was wanting to write a stored procedure which would identify queries
which
> are currently being blocked (spids 'blocked by' some other spid) when the
> stored procedure is being run.
> Is this possible? How do I go about figuring out how to do this?
>|||Heavey
Just as a starting point, I found that sysprocesses may be a good
place to start. I don't know what this does or anything, but it
looked relevant to the task:
> Select distinct spid, blocked, dbid from master..sysprocesses where blocked
>!= 0 for read only
>open DBA_lockinfo
>Declare @.spid varchar(5)
>Declare @.blocked varchar(5)
>Declare @.dbid varchar(10)
>Declare @.msg varchar (50)
>Declare @.event varchar(500)
>Declare @.event2 varchar(500)
>Create table #aux (EventType varchar(100), Parameters varchar(100),
>EventInfo varchar(500))
>Create table #aux2 (EventType varchar(100), Parameters varchar(100),
>EventInfo varchar(500))
>fetch next from DBA_lockinfo into @.spid, @.blocked, @.dbid
>While @.@.fetch_status = 0
>Begin
>Insert into #aux exec ('dbcc inputbuffer (' + @.spid + ')')
>Insert into #aux2 exec ('dbcc inputbuffer (' + @.blocked + ')')
>Set @.event = (select EventInfo from #aux)
>Set @.event2 = (select EventInfo from #aux2)
Good luck! Shoot me an email if you get a handle on it.
-Toby
On Tue, 21 Oct 2003 20:35:23 -0500, "Jim Heavey"
<JimHeavey@.hotmail.com> wrote:
>I was wanting to write a stored procedure which would identify queries which
>are currently being blocked (spids 'blocked by' some other spid) when the
>stored procedure is being run.
>Is this possible? How do I go about figuring out how to do this?
>|||Here is something I use. Show blocked spid and the path down to the one
causing all the waiting.
ALTER PROCEDURE dbo.ListBlockedTransactions
( @.ShowResults int = 1
)
as
SET NOCOUNT ON
-- created by M. Thomas Groszko
DECLARE BlockedSPIDSCursor CURSOR
FAST_FORWARD
FOR
SELECT BASE.SPID,
BASE.BLOCKED,
BASE.WAITTIME,
BASE.LASTWAITTYPE BLOCKED_LASTWAITTYPE,
CASE WHEN [CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME,
SCD.FIRSTNAME, SCD.MIDDLENAME, SCD.PREFERREDNAME) IS NOT NULL
THEN rtrim(BASE.hostname) + '-' +
[CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME, SCD.FIRSTNAME,
SCD.MIDDLENAME, SCD.PREFERREDNAME)
ELSE rtrim(BASE.hostname)
END BLOCKED_HOST
FROM SYSPROCESSES BASE
LEFT OUTER JOIN PCDB.dbo.PC PCBASE ON (BASE.hostname = PCBASE.MACHINEID)
LEFT OUTER JOIN PCDB.DBO.PERSON PERSONBASE ON (PCBASE.PERSONID =PERSONBASE.PERSONID)
LEFT JOIN CORPORATEDIRECTORY.dbo.PERSON SCD ON (PERSONBASE.CDID =SCD.PERSONID)
WHERE BASE.BLOCKED <> 0
ORDER BY BASE.last_batch DESC
FOR READ ONLY
DECLARE @.SampleTime DATETIME
SET @.SampleTime = GETDATE()
DECLARE @.BLOCKED_SPID INT
DECLARE @.BLOCKED_BLOCKEDBY INT
DECLARE @.BLOCKED_WAITTIME INT
DECLARE @.BLOCKED_LASTWAITTYPE VARCHAR(32)
DECLARE @.BLOCKED_HOST VARCHAR(255)
OPEN BlockedSPIDSCursor
FETCH BlockedSPIDSCursor INTO
@.BLOCKED_SPID,
@.BLOCKED_BLOCKEDBY,
@.BLOCKED_WAITTIME,
@.BLOCKED_LASTWAITTYPE,
@.BLOCKED_HOST
WHILE @.@.FETCH_STATUS = 0 -- while fetch returns a row
BEGIN
EXEC dbo.ShowBlockingSPID @.SampleTime, @.BLOCKED_BLOCKEDBY, 1
FETCH BlockedSPIDSCursor INTO
@.BLOCKED_SPID,
@.BLOCKED_BLOCKEDBY,
@.BLOCKED_WAITTIME,
@.BLOCKED_LASTWAITTYPE,
@.BLOCKED_HOST
END
CLOSE BlockedSPIDSCursor
DEALLOCATE BlockedSPIDSCursor
RETURN 0
go
ALTER PROCEDURE dbo.ShowBlockingSPID
( @.SampleTime DATETIME,
@.BlockingSPID int,
@.NestLevel int
)
as
SET NOCOUNT ON
-- created by M. Thomas Groszko
DECLARE @.BLOCKING_SPID INT
DECLARE @.BLOCKING_BLOCKEDBY INT
DECLARE @.BLOCKING_WAITTIME INT
DECLARE @.BLOCKING_LASTWAITTYPE VARCHAR(32)
DECLARE @.BLOCKING_HOST VARCHAR(255)
DECLARE @.NESTNEXTLEVEL INT
SELECT @.BLOCKING_SPID = BLOCKING.SPID,
@.BLOCKING_BLOCKEDBY = BLOCKING.BLOCKED,
@.BLOCKING_WAITTIME = BLOCKING.WAITTIME,
@.BLOCKING_LASTWAITTYPE = BLOCKING.LASTWAITTYPE,
@.BLOCKING_HOST =
CASE WHEN [CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME,
SCD.FIRSTNAME, SCD.MIDDLENAME, SCD.PREFERREDNAME) IS NOT NULL
THEN rtrim(BLOCKING.hostname) + '-' +
[CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME, SCD.FIRSTNAME,
SCD.MIDDLENAME, SCD.PREFERREDNAME)
ELSE rtrim(BLOCKING.hostname)
END
FROM SYSPROCESSES BLOCKING
LEFT OUTER JOIN PCDB.dbo.PC PCBLOCKING ON (BLOCKING.hostname =PCBLOCKING.MACHINEID)
LEFT OUTER JOIN PCDB.DBO.PERSON PERSONBLOCKING ON (PCBLOCKING.PERSONID =PERSONBLOCKING.PERSONID)
LEFT JOIN CORPORATEDIRECTORY.dbo.PERSON SCD ON (PERSONBLOCKING.CDID =SCD.PERSONID)
WHERE BLOCKING.SPID = @.BlockingSPID
IF @.BLOCKING_BLOCKEDBY <> 0
BEGIN
SET @.NESTNEXTLEVEL = @.NestLevel + 1
EXEC dbo.ShowBlockingSPID @.SampleTime, @.BLOCKING_BLOCKEDBY, @.NESTNEXTLEVEL
END
return 0
go
"Jim Heavey" <JimHeavey@.hotmail.com> wrote in message
news:O$u7BzDmDHA.2080@.TK2MSFTNGP10.phx.gbl...
> I was wanting to write a stored procedure which would identify queries
which
> are currently being blocked (spids 'blocked by' some other spid) when the
> stored procedure is being run.
> Is this possible? How do I go about figuring out how to do this?
>