0

SYSTEM: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

PROBLEM: Today I encountered an "orphaned transaction" (SPID -2) with a Unit of work ID of 00000000-0000-0000-0000-000000000000

I tried executing kill '00000000-0000-0000-0000-000000000000', but got a message that this could not be done. (Sorry, I failed to capture the exact message)

There were two transactions open in the MS DTC, but they were not set to in-doubt for a status, so I could not kill them from there.

At length, I resolved this by restarting the MS DTC.

This is a development system, so absolutely anything might have happened to cause this.

QUESTIONS: I was just wondering if anyone had seen it, and might have any insights into:

A) What might cause it
B) If there is a more elegant way to resolve

  • 1
    (Also, why are you still using 2008 R2 RTM? You should be on Service Pack 3.) – Aaron Bertrand Nov 24 '15 at 20:17
  • @ Aaron Bertrand - A very good question :-). I could drone on, but the short answer is that we obviously are falling down on our change management. – Joe Hayes Nov 24 '15 at 20:22
  • 2
    Well my point is, across three complete service packs, there might be a fix for this issue that doesn't require you to do anything. – Aaron Bertrand Nov 24 '15 at 20:22
  • Absolutely. The first thing MS Support will ask is if I'm on the latest SP. I'll check into it. I read your link, and had actually come across it in my own research. It's good info, but I was hoping someone might have gone deeper concerning the cause and, again, if there are any more elegant solutions. Thanks :) – Joe Hayes Nov 24 '15 at 20:24
3

The KILL command cannot kill a UOW of {00000000-0000-0000-0000-000000000000}, so that leaves you hanging on what to do next to get rid of it.

To get rid of spid -2 with the unit of work {00000000-0000-0000-0000-000000000000} you can do something, but it has side-effects:

Exec Sp_configure 'in-doubt xact transaction',2
-- This presumes ALL in-doubt trans are aborted
-- so it should be used with great caution.
GO
DBCC dbrecover('dbname'); -- or Alter database

I ran into this problem when test restoring databases. We had one database that restored successfully, but failed during the DBCC CHECKDB because of a {00000000-0000-0000-0000-000000000000} UOW.

In that case DTC was not running on the restore server at all, but still the {00000000-0000-0000-0000-000000000000} UOW was causing us problems as noted above. So you could say that this was an edge case.

Using the approach above finally cleared the problem for us, but if you use this technique you need to make sure that you are not aborting DTC connections that are in active use.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.