question

Kalebb avatar image
Kalebb asked

Filter Long Running Transaction

Hi All! I am a DBA Trainee with not much experience working with SQL Server. I was assigned to an improvement process as a test. I have to filter Long Running Transactions because we get some false alerts from those. We have a scheduled backup job that runs everyday and we get the lrt (Long Running Transaction), starting and stopping at the same time every day. My question is: Is there a way to filter that kind of Long Running transaction by modifying the monitoring script? If there's someone who can explain me the script step by step so I can have a better understanding about it It will be really appreciated. Here's the Script:

set nocount off  
  
--DECLARE @Path varchar(100)  
declare @Color char(6)  
declare @cmd varchar(400)  
declare @Header varchar(150)  
declare @Sql varchar(1000)  
declare @OutFile varchar(100)  
declare @TemplFile varchar(100)  
declare @DB varchar(150) ,@Rows int  
  
--set @Path = 'HERE GOES THE PATH (CONFIDENTIAL)'  
  
-------------------------------------------------------------------------------------------------------  
--   Start Process to have the long running transaciont for all the databases  
-------------------------------------------------------------------------------------------------------  
  
IF NOT EXISTS (select * from sysobjects where type = 'u' and name = 'open_transactions_history')  
Begin  
   
 CREATE TABLE dbo.open_transactions_history  
 (id int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,  
   Reportingserver varchar(100) NULL,  
   Opentran1 varchar(500) NULL,  
   DB varchar(100) null,  
   starttime1  varchar(100) null,  
   loginid varchar(100) null,  
   Hostname varchar(300) null,  
   ProgName varchar(500) null,  
   Status varchar(100) null  
 )  
  
 CREATE TABLE dbo.open_transactions_tmp  
 ( Reportingserver varchar(100) NULL,  
    Opentran1 varchar(500) NULL  
 )  
  
END  
 
declare DBS cursor fast_forward for  
select name from master..sysdatabases   
OPEN DBS  
  
FETCH NEXT FROM DBS INTO @DB  
WHILE @@FETCH_STATUS = 0  
 BEGIN  
  
  if DatabasePropertyEx(@DB,'Status')= null or DatabasePropertyEx(@DB,'Status')='RESTORING'  
   --Validate Database is not on Mirroring state, due if you database is on Mirroring mode and you run dbcc opentran on MSSQL 2005, you will get a fail msg.  
   print 'MirroringState'  
  else begin  
     
   if  DATABASEPROPERTY(@DB,'IsDboOnly')=0 and DATABASEPROPERTY(@DB,'IsInRecovery')=0 and DATABASEPROPERTY(@DB,'IsOffline')=0 begin  
    SET @cmd = 'DBCC OPENTRAN(' + @DB + ') WITH TABLERESULTS'  
     
    truncate table open_transactions_tmp  
    
    INSERT INTO open_transactions_tmp  
    EXEC(@cmd)  
      
    select @Rows=count(*) from open_transactions_tmp  
      
    if @Rows>1 begin  
    
       insert into open_transactions_history   
       select tt.Reportingserver,tt.Opentran1,@DB,  
       (select t.Opentran1 from  open_transactions_tmp t where t.Reportingserver = 'OLDACT_STARTTIME')   
       ,s.loginame,s.hostname,s.program_name,s.status  
       from open_transactions_tmp tt inner join master..sysprocesses s on tt.Opentran1=cast(s.spid as varchar(100))  
    
    
    end       
   end  
  end  
  
 FETCH NEXT FROM DBS INTO @DB  
  
 END  
  
CLOSE DBS  
DEALLOCATE DBS  
  
-------------------------------------------------------------------------------------------------------  
--   Finish Process to have the long running transaciont for all the databases  
-------------------------------------------------------------------------------------------------------  
  
-------------------------------------------------------------------------------------------------------  
--   NOTE:  
--   After 5 minutes transcurred this open transaction still is running will be displaying on BB page.  
--   Before 5 minutes transcurred this transaction gone, won't be displayed on BB Page.  
-------------------------------------------------------------------------------------------------------  
  
select @Rows=count(*) from open_transactions_history where Reportingserver='OLDACT_SPID' and DATEDIFF(minute,starttime1, getdate())>5  
  
-------------------------------------------------------------------------------------------------------  
--   Start process for creating out file for BB  
-------------------------------------------------------------------------------------------------------  
  
if @Rows=0  
 set @Color = 'green'  
else  
 set @Color = 'red'  
  
-- Generates output file Header  
set @Header = @Color + convert(char(10),getdate(),103) + ' ' + convert(char(25),getdate(),108) + @@SERVERNAME + ':'  
if @Color='red'  
 set @Header = @Header + ' SQL Long Running Transaction!'  
else  
 set @Header = @Header + ' SQL Long Running Transaction OK!'  
  
set @cmd = 'echo ' + @Header + ' > ' + @Path + '\sqllrt.'  
exec xp_cmdshell @cmd  
  
if @Color = 'red'  
begin  
 set @Sql = 'select distinct cast(Opentran1 as char(5)), '  
 set @Sql = @Sql + ' cast(starttime1 as char(20)), '  
 set @Sql = @Sql + ' cast(Status as char(11)), '  
 set @Sql = @Sql + ' cast(DB as char(40)), '  
 set @Sql = @Sql + ' cast(loginid as char(25)), '  
 set @Sql = @Sql + ' cast(HostName as char(11)), '  
 set @Sql = @Sql + ' cast(progname as char(20)) '  
 set @Sql = @Sql + 'from open_transactions_history '  
 set @Sql = @Sql + 'where Reportingserver='''+'OLDACT_SPID'+''''  
  
 set @OutFile = @Path + '\sqllrt.htm'  
 set @TemplFile = @Path + '\sqllrt.tpl'  
  
 exec sp_makewebtask  
  @outputfile = @OutFile,  
  @query = @Sql,  
  @templatefile = @TemplFile,  
  @dbname = 'master'  
  
 set @cmd = 'type ' + @Path + '\sqllrt.htm >> ' + @Path + '\sqllrt'  
  
 exec xp_cmdshell @cmd  
  
end  
  
  
-------------------------------------------------------------------------------------------------------  
--   Finish process for creating out file for BB  
-------------------------------------------------------------------------------------------------------  
  
drop table dbo.open_transactions_history  
drop table dbo.open_transactions_tmp  
transactiondbccmonitoring
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image Shawn_Melton commented ·
How about asking the person that wrote it?
1 Like 1 ·
Shawn_Melton avatar image Shawn_Melton commented ·
What version of SQL Server are you working with?
0 Likes 0 ·
Kalebb avatar image Kalebb commented ·
SQL Server 2005. The person who wrote it no loger works here.
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
That is one seriously scary way to check for long running transactions. I'd look at replacing it completely. However, if you want to add a check, here's what you need to do. You should modify this query as needed (probably with an EXISTS) and place it within the filtering section right below the first cursor: SELECT der.session_id FROM sys.dm_exec_requests AS der WHERE der.command = 'BACKUP DB' AND der.database_id = DB_ID(@DB);
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
@Grant Fritchey Sir, I really appreciate the fact that you looked into such a long script and provide the solution to that, which I did not even bother to look at :( Only one thing to mention that on recent builds of SQL Server 2005 i.e. SP4, the default value 'BACKUP DB' has been changed to 'BACKUP DATABASE'. (Same for SQL 2008 recent builds). Microsoft may be benchmarking it as SQL 2008 R2 and forget to update it in BOL. @Kalebb You are using few SQL 2000 dialects and as you have been suggested, you should consider restructuring it altogether. Now, for your current solution, my statement for the first cursor could be SELECT [name] FROM sys.databases AS D --USING sys.databases INSTEAD OF sysdatabases WHERE [D].[state] = 0 --0 = ONLINE AND [D].[user_access] = 0 --0 = MULTI_USER specified and the filter could be IF NOT EXISTS( SELECT 1 FROM sys.dm_exec_requests AS DER WHERE([DER].[command] = N'BACKUP DATABASE' OR [DER].[command] = N'BACKUP LOG' --YOU MAY NOT NEED THIS FILTER OR [DER].[command] = N'DBCC' --YOU MAY NOT NEED THIS FILTER ) AND [DER].[database_id] = DB_ID(@DB) ) I have added some additional filters, which you can consider.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.