SQL 2005 Transactions

[qsac]

I have a transaction that is blocking most activity on my server. In activity monitor is just shows the transacion starting.

Is there a way to get more info on that process id? what stpred proc is the issue?

ANy info is appreciated.

Thanks,
Q

 

[mrdenny]

Use SQL Profiler to see what commands are being run.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

use sp_who2 to get the spid then put the spid in this code.

declare @handle binary(20)
select @handle = sql_handle 
from master.dbo.sysprocesses where spid = [COLOR=red]236[/color]
select * from ::fn_get_sql(@handle)

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Couldn't you just run the sp_who2 and then DBCC InputBuffer(<spid>) to get the last command run by that SPID?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

DBCC INPUTBUFFER only gives you the first 255 characters of the command. So if the command is longer than that it gets truncated.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Ahh. Good point.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

It happens on occasion.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Hee. @=)


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"