You are here:Home » tsql » Find Oldest Active User Transaction and kill them

Find Oldest Active User Transaction and kill them

---To kill oldest active open transaction automatically ---

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