September 5, 2007 at 12:30 pm
We have been plagued by the blocking of spid -2 in a few of our environments (Prod, QA & Dev). Normally we see this happen when there is an SSIS package that is running and the DTC is involved. We have fixed those scenarios and those don't happen anymore.
Now we are having this happen just by running a simple select * from a table on a specific table (which by the way was not involved in any of the SSIS packages where this was happening).
I have turned on profiler to see what else is going on and we get nothing. The select comes across in the trace, but nothing else.
Getting very frustrated, anybody else run into this? If so, how do we troubleshoot this?
TIA,
-A.
September 6, 2007 at 1:16 am
Spid -2 is normally caused by orphened DTC transactions. I get them often from some JDBC queries. I can't think of any reason you should gt them from a select on a local table.
Run the following query
SELECT * from master..syslockinfo WHERE req_spid = -2
What kind of lock is it (S, X, U, ..)
What object is it on? (There'll be an objectid. Use Object_name(id) to get the name)
What it the req_transactionUOW (it's a GUID. 0 or non 0?)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply