Orphaned Distributed Transactions

----- We occasionally get orphaned transactions when unit tests fail.
----- This will kill the orphaned transactions for a specific database,
-----      or all on the server without having to restart the Distributed
-----      Transaction Coordinator (which could cause successfully executing
-----      unit tests within a different database to fail).

DECLARE @DBName sysname
--SET @DBName = 'Bob'
 
DECLARE @KILL varchar(1024)
DECLARE @currentUOW uniqueidentifier
SET @currentUOW = (SELECT TOP 1 req_transactionUOW
                   FROM sys.syslockinfo
                   WHERE ( rsc_dbid = db_id(@DBName) OR @DBName IS NULL)
                          AND req_spid = -2
                          AND rsc_objid <> 0 
                   ORDER BY req_transactionUOW)
 
WHILE @currentUOW IS NOT NULL BEGIN
   SET @KILL = 'KILL ''' + cast(@currentUOW AS varchar(40)) + ''';'
   PRINT @KILL
   EXEC ( @KILL )
   SET @currentUOW = (SELECT TOP 1 req_transactionUOW
                      FROM sys.syslockinfo
                      WHERE (    rsc_dbid = db_id(@DBName) OR @DBName IS NULL)
                             AND req_spid = -2
                             AND rsc_objid <> 0
                             AND req_transactionUOW > @currentUOW
                      ORDER BY req_transactionUOW)
END