Can I View Active Transaction Details?

[Kiehlster]

We have one website that's been really putting the anvil on our sql2000 server and it only happens once in a blue moon, so it's really hard to track down or even worth our while to put much time into.

I was just wondering if there's some way I can view the full query that is being sent to the server when it decides to peg to 100% for hours. I can view the current process list and see what database it is and what type of query (SELECT) but I can't seem to find any way to view the exact details of that query. Is there some hidden place in the enterprise manager to view the currently active queries fully written out? If I can do that, I can probably find where that query is and reoptimize it or prevent it from happening in these rare conditions.

Steve Kiehl
Web Page Designer - Nanovox Productions
Fantasy Artist - Zeadi

 

[kaht]

Management > Current Activity > Process Info
Then right click the connection that is "pegging" the server and select properties. In that window it should show the last SQL command to run from that connection.

-kaht

How much you wanna make a bet I can throw a football over them mountains?

 

[SQLDenis]

That doesn't give you the whole command (gets truncated)
Same as running sp_who2 and then DBCC INPUTBUFFER (SPID)
Maybe run a trace for a while

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

That calls DBCC INPUTBUFFER( spid ) that returns only first 255 chars of last T-SQL batch... this may or may not be enough to determine where that query is.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)