Help stop my spinning wheels on user sql

Can query sysprocesses to get user information .Can find the top
blocker
but have spun wheels for a long long time unable to find sql run by top
blocker.
Any body done this before or has directions to scripts that can do this
Your input is highly appreciated
MassaTry:
select
p1.*
from
dbo.sysprocesses p1
where exists
(
select
*
from
dbo.sysprocesses p2
where
p2.blocked = p1.spid
)
and p1.blocked in (0, p1.spid)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<mngong@.gmail.com> wrote in message
news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
Can query sysprocesses to get user information .Can find the top
blocker
but have spun wheels for a long long time unable to find sql run by top
blocker.
Any body done this before or has directions to scripts that can do this
Your input is highly appreciated
Massa|||Thanks Dr Tom
Needed the complete sql that has been my bete noire.
at the time the blocking occured
Tom Moreau wrote:
> Try:
> select
> p1.*
> from
> dbo.sysprocesses p1
> where exists
> (
> select
> *
> from
> dbo.sysprocesses p2
> where
> p2.blocked = p1.spid
> )
> and p1.blocked in (0, p1.spid)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <mngong@.gmail.com> wrote in message
> news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
> Can query sysprocesses to get user information .Can find the top
> blocker
> but have spun wheels for a long long time unable to find sql run by top
> blocker.
> Any body done this before or has directions to scripts that can do this
> Your input is highly appreciated
> Massa|||In that case, you'd pretty much need to be running the profiler at the time
it occurred.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<mngong@.gmail.com> wrote in message
news:1153234728.918733.70910@.35g2000cwc.googlegroups.com...
Thanks Dr Tom
Needed the complete sql that has been my bete noire.
at the time the blocking occured
Tom Moreau wrote:
> Try:
> select
> p1.*
> from
> dbo.sysprocesses p1
> where exists
> (
> select
> *
> from
> dbo.sysprocesses p2
> where
> p2.blocked = p1.spid
> )
> and p1.blocked in (0, p1.spid)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <mngong@.gmail.com> wrote in message
> news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
> Can query sysprocesses to get user information .Can find the top
> blocker
> but have spun wheels for a long long time unable to find sql run by top
> blocker.
> Any body done this before or has directions to scripts that can do this
> Your input is highly appreciated
> Massa|||I can't tell if you're using SQL Server 2005 here, but if you are, you might
be interested in the 'blocked process threshold' parameter of sp_configure.
On our testing servers I have a trace going that continually monitors for
these problem areas. What gets returned is an XML block which details the
blocking and blocked processes - very nice indeed. If you're on SQL Server
2000, the sp_blocker_pss80 scripts would be worth looking at
(http://support.microsoft.com/?id=271509).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com