Blocked transactions - how to identify the sql commands?

Hi SQLServer gurus :)

Just wondering if anyone can give me some info on how to find out the full syntax of the commands executed by the blocked and blocking SPID's in a locking situation.

Using sp_who or sp_who2 will give basic info on the blocked trans (such as DELETE, SELECT etc), but not the actual statement. The blocking spid's command is only showing AWAITING COMMAND.

Not really an urgent problem, but any suggestions appreciated!

Cheers,
MeganDBCC INPUTBUFFER
Displays the last statement sent from a client to Microsoft SQL Server.

Syntax
DBCC INPUTBUFFER (spid)

You can use this command to see what is the longest running command which may point to the transaction that is causing the blocking.

DBCC OPENTRAN
Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

Syntax
DBCC OPENTRAN
( { 'database_name' | database_id} )
[ WITH TABLERESULTS
[ , NO_INFOMSGS ]
]|||Thanks for your response, achorozy.

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?
>