Re: Connecting Trace info to blocked users?

laurenq uantrell (laurenquantrell@xxxxxxxxxxx) 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@xxxxxxxxxxxxx

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

.

Relevant Pages

  • Connecting Trace info to blocked users?
    ... Loginame is causing the block. ... command that the offending blocker is running to cause the block, ... it only says "SELECT" etc. with no details about the stored procedure ... including the parameters that every cmd is running. ...
    (comp.databases.ms-sqlserver)
  • Re: generating sql scripts of stored procedures
    ... What could be causing this ... > Whenever I build a new stored procedure into my database I specifically ... > at the end of the script. ... All my settings in sql server and database ...
    (microsoft.public.sqlserver.programming)
  • Help with error
    ... Does anyone know what is causing this? ... stored procedure in an iseries. ... Dim cmd As New iDB2Command(sql, ... Joe in Florida ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Automated mail merge from a stored procedure
    ... I found what was causing my problem & thought I'd post in case anyone ... experiences the same problem. ... My stored procedure had a "Print" statement in it that I'd been using ... Peter Carpenter. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Open Linked Sites
    ... causing the problem, never happened with SP-1 ... > I have disabled pop up blocker and it still doesn't want to play ball ...
    (microsoft.public.windowsxp.general)