x

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

more ▼

asked May 30, 2012 at 11:25 PM in Default

avatar image

Kalebb
0 1 1 1

How about asking the person that wrote it?

May 31, 2012 at 12:34 AM Shawn_Melton

What version of SQL Server are you working with?

May 31, 2012 at 12:36 AM Shawn_Melton

SQL Server 2005. The person who wrote it no loger works here.

May 31, 2012 at 07:58 AM Kalebb
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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);

more ▼

answered May 31, 2012 at 10:11 AM

avatar image

Grant Fritchey ♦♦
137k 20 42 81

@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.

May 31, 2012 at 12:45 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x72
x52
x32

asked: May 30, 2012 at 11:25 PM

Seen: 1777 times

Last Updated: May 31, 2012 at 12:45 PM

Copyright 2016 Redgate Software. Privacy Policy