SQL Server – Deleting Distributed Orphaned Transactions

11 July,2012 by Jack Vamvas

I outlined in MS DTC and sys.dm_tran_active_transactions  and SQL Server detected a DTC/KTM in-doubt transaction with UOW , how orphaned distributed transactions are created.  This query deals with alleviating the immediate problem . Root cause analysis should follow.

Depending  on the application , the orphaned transactions count can rise quickly. For example , a Document Management system integrating emails into an archive can create some high voltage sql server blocking. As the blocking increases other users can’t add records. If it’s a critical system , the helpdesk is suddenly busy with complaining users.

Problems are exacerbated on the type of lock . If  a request takes an Exclusive Lock , and another resource requests the resource , the requesting resource is blocked. In the meanwhile , if there is an application level query timeout  , the transaction at the SQL Server level loses it’s parent transaction call.

The DBA has to clear out the orphaned transactions. Clearing out a few is straightforward, but  a large amount is time consuming. The query below , will create the KILL statements ,  from the sys.dm_tran_locks  view, based on request_session_id = -2. The value of -2 indicates an orphaned distributed transaction.

Excecute the SELECT statement , this generates the KILL statements. Excecute the KILL statements. I prefer to complete this task in 2 steps

select distinct 'KILL ''{' + CAST(request_owner_guid AS NVARCHAR(255)) + '}''' from sys.dm_tran_locks  where request_session_id = -2

--generates this output
--execute thse statements
KILL '{13FD0513-ED7B-4CA5-B4AA-C77D8969F947}'
KILL '{989722D5-F5C1-4E67-8BF1-3C1CFD00C234}'
KILL '{1EA4CCE1-FD20-4C90-A8EA-6C6C270E413A}'
KILL '{41CFD5A7-857E-43A6-BDC1-4F8821623E20}'
KILL '{74627131-090A-49B6-9BFE-1AC640EE3A33}'
KILL '{3D89BACE-745E-43B0-9583-F20CE29D4658}'

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server – Deleting Distributed Orphaned Transactions

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer