SQL Server – Msg 6112, Level 16, State 1- Distributed transaction with UOW

Some time ago, I was trying to kill negative spid in sql server but the kill statement was ineffective. I was getting error saying “Microsoft Distributed Transaction Coordinator can resolve this transaction. Kill command failed”

SQL Server Error – Distributed transaction with UOW

Msg 6112, Level 16, State 1, Line 1
Distributed transaction with UOW {BABC4276-DBA0-45EF-AC87-FFD587FF2650} is in prepared state.
Only Microsoft Distributed Transaction Coordinator can resolve this transaction. KILL command failed.

As suggested by error, this is what we did to resolve this issue. When the Kill command doesn’t work

  1. Connect to SQL Server (Physical Box) – click on-> Start -> Run
  2. Enter dcomcnfg, and press enter
  3. Navigate to Component Services ->Computers -> My Computer ->Distributed Transaction Coordinator ->Local DTC ->Transaction List
  4. In the centre panel, you’ll see all of the open transactions. The one that have a question mark icon are “in-doubt” transactions.
  5. Right-click the panel and change the view to Details. This will show you the UOW ID column for the transactions.
  6. Find the in-doubt transaction where the UOW matches from below query – this is the transaction that we want to work with.
  7. Right-click this transaction, select “Resolve” and select “Abort”. This will kill the transaction
  8. Confirm by running below query again.

select  distinct convert (smallint, req_spid) As spid,
DB_NAME(rsc_dbid) As dbname,
‘kill  ”’+ cast (req_transactionUOW as varchar(50)) +”” as KillCommand
from  master.dbo.syslockinfo
where req_spid < 0 and req_transactionUOW <> ‘00000000-0000-0000-0000-000000000000’

 

Leave a Reply

  

  

  


3 + 5 =