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

Kalebb gravatar 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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

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

x60
x47
x18

asked: May 30, 2012 at 11:25 PM

Seen: 1551 times

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