Find Longstanding Open Transactions

,

Ever forget to commit a transaction and then find out hours later that there is deadlocked transactions all over your database server? Worse yet has one of your coworkers done this to your database server? Never again, this stored procedure will net send the machine that has an open transaction, and send you (The DBA) an email message about who or what has the open transaction. The email message could easily be sent to a pager. It also creates a table called open_transactions_history that stores historical data for you. Simply add the stored procedure and then set it up as a reoccurring job. If you want to report on transactions that have been open for 15 - 30 Minutes set it up to run every 15 Minutes. Enjoy 🙂

CREATE PROCEDURE FindOpenTransactions AS

declare @emailAddress varchar(128)
set @emailAddress = 'youremail@whereever.com'

--Create the required Tables if they do not exist
IF NOT EXISTS (select * from sysobjects where type = 'u' and name = 'open_transactions')
Begin
	CREATE TABLE open_transactions
	(spid int NULL,
	login varchar(32) NULL,
	db varchar(128) NULL,
	hostname varchar(64) NULL)
end

IF NOT EXISTS (select * from sysobjects where type = 'u' and name = 'open_transactions_history')
Begin
	CREATE TABLE dbo.open_transactions_history
	(Found_Date datetime NULL,
	spid int NULL,
	login varchar(32) NULL,
	db varchar(128) NULL,
	hostname varchar(64) NULL,
	program_name varchar(128) NULL)
end

declare c4 cursor for select spid,dbid,hostname,loginame,program_name from master..sysprocesses where open_tran > 0
declare  @spid int, @hostname varchar(64), @login varchar(32), @cmd varchar(4000), @database varchar(128), @program_name varchar(128), @dbid int, @spidlist varchar(2000), @wehavedata int
select @spidlist = ''
open c4
fetch next from c4 into @spid,@dbid,@hostname,@login, @program_name
while @@fetch_status = 0
begin 
set @wehavedata = 1
select @database = name from master..sysdatabases where dbid = @dbid
If exists (select * from open_transactions where spid = @spid and login = @login and db = @database and hostname = @hostname)
	Begin
	insert into open_transactions_history (Found_Date,spid,login,db,hostname,program_name) values (getdate(),@spid,@login,@database,@hostname,@program_name)
	select @cmd = 'net send ' + @hostname + '"Your machine has an open transaction to ' + @database + '"'
	exec master..xp_cmdshell @cmd
	select @cmd = 'Host: ' + @hostname + char(13)
	select @cmd = @cmd + 'Login: ' + @login + char(13)
	select @cmd = @cmd + 'Database: ' + @database + char(13)
	select @cmd = @cmd + 'SPID: ' + convert(varchar(6),@spid) + char(13)
	select @cmd = @cmd + 'Program: ' + convert(varchar(6),@program_name) + char(13)
	exec master..xp_sendmail @recipients = @emailAddress, @subject = 'Open Transactions', @message = @cmd
	End
else 
insert into open_transactions (spid,login,db,hostname) values (@spid,@login,@database,@hostname)

select @spidlist = @spidlist + convert(varchar(32),@spid) + ','
fetch next from c4 into @spid,@dbid,@hostname,@login, @program_name
end

--Cleanup

if @wehavedata = 1
begin
	select @spidlist = substring(@spidlist,1,len(@spidlist)-1)
	select @cmd = 'delete from open_transactions where spid not in (' + @spidlist + ')' + char(13)
	print @cmd
	exec (@cmd)
end
else
	delete from open_transactions

close c4
deallocate c4
GO

Rate

4 (1)

Share

Share

Rate

4 (1)