Open Transaction Alarm to Log to the Event log
Example is a sharepoint crawl that should complete in 20 min but seems to hang sometimes and then interfer with other operations. The Share Point Admin can stop or pause a crawl if it is causing any issues on the system. The script will log to the event logs using xp_logevent and a 3rd party tool can then geneate a ticket. You can modify the block of code to a send a page or an email using the Database mail. This can run as a T-SQL job step with in a SQL Agent Job and run every hour. You can also modify the time to alarm on the open transaction. Hope you find this usefull. We used this as a counter measure from an incident.
--DBCC OPENTRAN
--select * from master..sysprocesses where open_tran <>0
SET NOCOUNT ON
Declare @srvname varchar(55),@message varchar(255),@hh varchar(25),@mm varchar(25)
Set @srvname = @@servername
Select Replace( @srvname,'\','_')
--Print @srvname
select spid, dbid,login_time,last_batch, status,hostname,program_name,loginame
into #opentran from master..sysprocesses where open_tran <> 0
Select * from #opentran
if @@ROWCOUNT <> 0
Begin
Declare @compare_time datetime,@last_batchtime datetime,@last_batchtimediff datetime
--Select @compare_time = dateadd(hh,-4,Getdate())
Select @hh = DATEDIFF(hh,last_batch,@compare_time) FROM #opentran where DATEDIFF(mi,last_batch, @compare_time)>60
Select @mm = DATEDIFF(mi,last_batch,@compare_time) FROM #opentran where DATEDIFF(mi,last_batch, @compare_time)>60
Select @compare_time = Getdate()
Select spid,db_name(dbid) AS 'DBName',login_time=Convert(varchar,login_time,100),last_batch=Convert(varchar,last_batch,100),
DATEDIFF(hh,login_time,last_batch)AS 'Hours Between Last Batch and login time',
status,hostname,program_name,loginame
FROM #opentran where DATEDIFF(mi,last_batch, @compare_time)>60
IF @@ROWCOUNT <> 0
Begin
Print 'It works'
Select @message = ' SharePoint Server ' + @srvname + ' has an open transaction longer then 1 hour' --+ @hh + ' hours ' + @mm + ' minutes'
Print @message
exec xp_logevent 50205 , @message , 'Warning'
Drop table #opentran
END
else
Drop table #opentran
END
