sspKillOldTransactions

GO
/****** Object:  StoredProcedure [dbo].[sspKillOldTransactions]    Script Date: 04/16/2014 20:01:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		A.
-- Create date: 201100621
-- Description:	Kill Open Transactions
-- =============================================
ALTER PROCEDURE [dbo].[sspKillOldTransactions]
	
AS
BEGIN
	
	SET NOCOUNT ON;

	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) 
	INSERT INTO dbo.tblKilledTransactions (SpID) VALUES (@spid)
	FETCH NEXT FROM c_trans INTO @spid
	END
	CLOSE c_trans
	DEALLOCATE c_trans 
	   
	   
END