|
|
Posted by Erland Sommarskog on 10/01/16 11:27
laurenq uantrell (laurenquantrell@hotmail.com) writes:
> In master.dbo.sysprocesses 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.sysprocesses, 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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|