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}'
