Query to find oldest active user transaction and kill them

Use below query to find oldest active user transaction and kill them:


DECLARE @sql varchar(8000)
DECLARE @spid VARCHAR(4000)


DECLARE c_trans cursor FAST_FORWARD FOR 
select est.session_id from sys.dm_tran_active_transactions tas 
inner join sys.dm_tran_database_transactions tds on (tas.transaction_id = tds.transaction_id )
inner join sys.dm_tran_session_transactions est on (est.transaction_id=tas.transaction_id)
where est.is_user_transaction =1 and tas.transaction_state =2
and tds.database_transaction_begin_time is not null


OPEN c_trans 
FETCH NEXT FROM c_trans INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'kill '+ @spid
EXEC(@SQL)
FETCH NEXT FROM c_trans INTO @spid
END 
CLOSE c_trans
DEALLOCATE c_trans