Connecting Trace info to blocked users?

[laurenq uantrell]

In master.dbo.sysp rocesses I can filter for blocked users (Blocked >0)
and I can create a self join(ON SPID = Blocked) to see what user
Loginame is causing the block. In the column [cmd] I can see the
command that the offending blocker is running to cause the block, but
it only says "SELECT" etc. with no details about the stored procedure
that is causing the block.

If I am running a Trace, I can see the exact stored procedures
including the parameters that every cmd is running.

Is there a way to see that same Trace information when looking for
blocked users in master.dbo.sysp rocesses, or in some other place?

Ideally what I want is a list of blocked users, who is causing the
blocks and the stored procedure name (or other mischief) causing the
block.

Any help is appreciated.
lq

[SQL]

You can do this, run sp_who2
there is a field named BlkBy grab the id
run DBCC INPUTBUFFER (ID) to get the SQL statement

http://sqlservercode.blogspot.com/

[laurenq uantrell]

Is there a way to get the EXEC ('DBCC INPUTBUFFER (BlkBy) WITH
NO_INFOMSGS')) to become a column in the resulting SELECT statement?

CREATE TABLE #SystemUsers (id int identity, SPID int, BlockerLoginame
nvarchar(255), BlockingSQL nvarchar(2000))
INSERT INTO #SystemUsers (SPID, BlockerLoginame , BlockingSQL)
SELECT
SPID,
loginame,
/* (EXEC ('DBCC INPUTBUFFER (BlkBy) WITH NO_INFOMSGS')) As BlockingSQL
??????? */
FROM master.dbo.sysp rocesses

SELECT
s.spid,
status,
loginame,
hostname,
cmd,
cpu,
last_batch,
login_time,
kpid,
blocked,
x.BlockerLogina me,
waittype,
waittime,
lastwaittype,
waitresource,
dbid,
uid,
memusage,
ecid,
open_tran,
sid,
hostprocess,
nt_domain,
nt_username,
net_address,
net_library,
context_info
FROM master.dbo.sysp rocesses s
LEFT OUTER JOIN #SystemUsers x ON x.SPID = s.Blocked
WHERE Blocked >0

[SQL]

Insert the result of sp_who2 into a table
Delete everything where BlkBy is empty
Loop thru this table and insert the DBCC inputbuffer result into
another table
Join this last table with sysprocesses

http://sqlservercode.blogspot.com/

[laurenq uantrell]

This is the part I'm not sure how to write:
"Loop thru this table and insert the DBCC inputbuffer result into
another table"

[Erland Sommarskog]

laurenq uantrell (la************ *@hotmail.com) writes:

In master.dbo.sysp rocesses I can filter for blocked users (Blocked >0)
and I can create a self join(ON SPID = Blocked) to see what user
Loginame is causing the block. In the column [cmd] I can see the
command that the offending blocker is running to cause the block, but
it only says "SELECT" etc. with no details about the stored procedure
that is causing the block.

If I am running a Trace, I can see the exact stored procedures
including the parameters that every cmd is running.

Is there a way to see that same Trace information when looking for
blocked users in master.dbo.sysp rocesses, or in some other place?

Ideally what I want is a list of blocked users, who is causing the
blocks and the stored procedure name (or other mischief) causing the
block.

More so, provided that the stored procedures are not encrpyted, you
can get the exact SQL code they are executing. And, no, no need for a
lot of coding. I've already done it for you. :-)

http://www.sommarskog.se/sqlutil/aba_lockinfo.html sounds exactly what
you are looking for.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Simon Hayes]

laurenq uantrell wrote:

This is the part I'm not sure how to write:
"Loop thru this table and insert the DBCC inputbuffer result into
another table"

I admit I didn't read your post in detail, but I suspect that
fn_get_sql() is what you're looking for - it's more useful than DBCC
INPUTBUFFER. See Books Online for more details; if it's not mentioned in
your current version of BOL then you should get the latest version from
here:

http://www.microsoft.com/sql/techinf...2000/books.asp

But since Erland uses this function in the procedure that he mentioned,
you might well find it easier just to use that.

Simon

[laurenq uantrell]

Whew. That's a hefty amount of code, which seems much more complex
than:
"Insert the result of sp_who2 into a table
Delete everything where BlkBy is empty
Loop thru this table and insert the DBCC inputbuffer result into
another table
Join this last table with sysprocesses"

Thanks for that.
Is there a way to force some blocking so that I can test it?

[laurenq uantrell]

The next step is to run this every 60 seconds and export the results to
a text file using bcp...
I know that in my original post:

SELECT
SPID,
loginame
FROM master.dbo.sysp rocesses
WHERE
BlkBy > 0

will get the ball rolling if a blocking situation exists.

But looking at your aba_lockinfo sproc I'm not sure where to start
something like:
if exists (SELECT * FROM master.dbo.sysp rocesses WHERE BlkBy > 0)
begin
/* Blocking exisit so use Erland's aba_lockinfo sproc to BCP to a
text file */
end

[Erland Sommarskog]

laurenq uantrell (la************ *@hotmail.com) writes:

Whew. That's a hefty amount of code, which seems much more complex
than:
"Insert the result of sp_who2 into a table
Delete everything where BlkBy is empty
Loop thru this table and insert the DBCC inputbuffer result into
another table
Join this last table with sysprocesses"

Thanks for that.
Is there a way to force some blocking so that I can test it?
In one window:

BEGIN TRANSACTION
CREATE TABLE #tmp (a int NOT NULL)

In other another

SELECT * FROM tempdb..sysobje cts
The next step is to run this every 60 seconds and export the results to
a text file using bcp...

BCP with queryout would work in theory, but queryout is known to be
troublesome. The fact the column lengths may very from execution to
execution may also be aggrevate things.

You could also create a table that matches the output from aba_lockinfo,
and then run INSERT EXEC to that table. Again, the dynamic field lengths
could cause some problems, but just make your columns wide enough.

But I would rather just run it from OSQL with output directed to a file.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp