SQL Transaction "-2"

Question

SQL Transaction "-2"

Bruce A Conklin

SSC Journeyman

Points: 76
More actions
August 26, 2008 at 5:48 pm

#191663

I have encountered a problem for which I find no references.
One particular application our firm runs will develop a blocking transaction. This is not unusual in itself but this particular blocking transaction is always identified as transaction number "-2." It, of course does not display in the list of transactions in Activity Monitor nor in a list generated by sp_who2. But, when this transaction appears, it blocks anything that follows it and it cannot be "killed." The only way to clear this transaction is to stop and restart SQL Server.
How often does this problem occur? Well, it has only happened twice in 18 months of running this application but it has happened on different servers. The explanation of a "ghost transaction" doing the blocking is met with glances suggesting incompetence. ;=}
Have any of you encountered this problem before? Any suggestions?
"Sacramento" Bruce Conklin

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Spid's with an ID of -2 or orphaned sessions and you need to clean them up manually.

Unfortunately you can't kill them the easy way by running Kill -2 ,but you need to find the Unit of Work ID from the syslockinfo table. You can find more info here http://technet.microsoft.com/en-us/library/aa275788(SQL.80).aspx and here http://msdn.microsoft.com/en-us/library/aa933230(SQL.80).aspx

[font="Verdana"]Markus Bohse[/font]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

To expand a bit, they are orphaned distributed transactions. What happens is that some app uses DTC in it's communications with SQL and then doesn't properly commit or rollback the distributed transaction. I've seen it a fair bit with apps using JDBC.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bruce A Conklin SSC Journeyman Points: 76 More actions · Answer 3

Thanks Gail, the only app we have causing this problem is a Java app that relies heavily on DTC. Fortunately, the problem only seems to arise when we are in a testing phase (either post-migration or qualifying testing in QA) but you have provided a great clue.

"Sacramento" Bruce Conklin

Bruce A Conklin SSC Journeyman Points: 76 More actions · Answer 4

Thanks Markus, those page links were really helpful.

"Sacramento" Bruce Conklin