September 24, 2014 at 10:24 pm
Comments posted to this topic are about the item Monitoring Longest Running Transaction using SQL Server Agent Alerts
September 25, 2014 at 6:20 am
Interesting article. I could really use something like this. I attempted to run the TSQL and it blew up:
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near '.'.
my @@version: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
September 25, 2014 at 6:48 am
Hi SSC-Enthusiastic,
Thanks for the reply. I hope complete code was not copied. I did ran it on 2008 R2 Server. It went fine. Please check again, may be some quote issue.
September 25, 2014 at 8:21 am
In retrospect, I do not have full rights to fire off all the SYS tables - That is more than likely my issue.
eg.: sys.dm_tran_database_transactions
I know there was a caveat in the article that detailed this.
September 25, 2014 at 8:42 am
Can you add code which would test this alert? I'm trying to verify functionality on my local box, and getting no emails and no recorded occurrences in the history of alert.
I was trying:
begin tran
insert some data
waitfor delay '00:01:00'
September 25, 2014 at 8:52 am
Just minor note but it appears one of your images did not take, your first "alert" image is repeated when "Select Transactions as the Object and set the Counter to Longest Transaction Running Time".
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 25, 2014 at 8:58 am
As you noted, using that counter only tracks transactions with "read committed snapshot isolation level." Does anyone know of a way to monitor and alert on ANY long running query, regardless of isolation level etc?
September 25, 2014 at 9:04 am
louislamour2673 (9/25/2014)
As you noted, using that counter only tracks transactions with "read committed snapshot isolation level." Does anyone know of a way to monitor and alert on ANY long running query, regardless of isolation level etc?
Modify or use the query provided in this article and wrap it around logic that sends an email if a transaction is found over your threshold. I use a stored procedure that first finds if there is a long running transaction:
SELECT @longRunningTransaction = MAX(DATEDIFF(n, a.transaction_begin_time, GETDATE()))
FROM sys.dm_tran_active_transactions a
INNER JOIN sys.dm_tran_session_transactions s ON a.transaction_id = s.transaction_id;
Then if that is over my threshold I send an email out with an attachment of session activity on the instance. Which you could use output from sp_WhoIsActive or something, or just write your own detailed query to pull what info you want.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 25, 2014 at 10:39 am
I CAN NOT GET ANY ALERT...THE ARTICLE IS NOT MUCH CLEAR TO UNDERSTAND LIKE NEW DBAS...
AND PLEASE GIVE ME ONE EXAMPLE...BY USING UR SCRIPT TO GET ALERT..
or I can not understand u have selected execute job option in response tab..so please let us know
the job only have that mentioned query ? or we need to schedule that job every one min?
because i m getting alert once scheduled job for every one min with mentioned query...
Thanks
September 26, 2014 at 6:27 am
Make the alert, in the tab response choose 'New job', make the job, executing the following script:
DECLARE @subject AS varchar(100) = 'Transaction longer than 60 seconds'
DECLARE @message AS varchar(130) = 'All open transactions'
DECLARE @receivers AS varchar(100) = 'your@mail.com'
DECLARE @querytran AS varchar(MAX) = 'SELECT b.session_id AS [Session ID],
CAST(Db_name(a.database_id) AS VARCHAR(20)) AS [Database Name],
c.command,
Substring(st.TEXT, ( c.statement_start_offset / 2 ) + 1,
( (
CASE c.statement_end_offset
WHEN -1 THEN Datalength(st.TEXT)
ELSE c.statement_end_offset
END
-
c.statement_start_offset ) / 2 ) + 1)
statement_text,
Coalesce(Quotename(Db_name(st.dbid)) + N''.'' + Quotename(
Object_schema_name(st.objectid,
st.dbid)) +
N''.'' + Quotename(Object_name(st.objectid, st.dbid)), '''')
command_text,
c.wait_type,
c.wait_time,
a.database_transaction_log_bytes_used / 1024.0 / 1024.0 AS [MB used],
a.database_transaction_log_bytes_used_system / 1024.0 / 1024.0 AS [MB used system],
a.database_transaction_log_bytes_reserved / 1024.0 / 1024.0 AS [MB reserved],
a.database_transaction_log_bytes_reserved_system / 1024.0 / 1024.0 AS [MB reserved system],
a.database_transaction_log_record_count AS [Record count]
FROM sys.dm_tran_database_transactions a
JOIN sys.dm_tran_session_transactions b
ON a.transaction_id = b.transaction_id
JOIN sys.dm_exec_requests c
CROSS APPLY sys.Dm_exec_sql_text(c.sql_handle) AS st
ON b.session_id = c.session_id'
EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name='Database Mail', @recipients=@receivers, @subject=@subject, @body=@message, @query=@querytran, @execute_query_database='master', @query_no_truncate=1
Of course you need to setup mailing (@profile_name must be a valid profile).
@receivers is semicolon-separated, enter mailaddresses as necessary.
September 26, 2014 at 8:51 am
There is another approach to Monitoring long Running Transactions implemented through store procedure and SQL job executing procedure on predefine schedule.
Procedure will be executed against all databases on given SQL server and will notify operator if there is an long running transaction in database(s).
Notification will be send directly from store procedure using msdb.dbo.sp_send_dbmail.
Below are steps to implement monitoring along with procedure source code and code for SQL job.
1.Create store procedure - code below
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_DetectLongRunningTransactions]
@OpenTransactionTreshold int = null
as
set nocount on
if @OpenTransactionTreshold is null or LEN(@OpenTransactionTreshold) <= 0
BEGIN
print 'Open Transaction treshold has to be provided in seconds'
return -1
END
DECLARE @TROUBLE_SPID INT
DECLARE @TRANSACTION_START_TIME datetime
if OBJECT_ID('tempdb..#OpenTranStatus') > 0
BEGIN
truncate table #OpenTranStatus
END
else
BEGIN
CREATE TABLE #OpenTranStatus (ID INT IDENTITY (1,1),
ActiveTransaction varchar(25),
Details sql_variant
)
END
-- Execute the command, putting the results in the table.
INSERT INTO #OpenTranStatus
EXEC ('DBCC OPENTRAN (0) WITH TABLERESULTS, NO_INFOMSGS');
declare @body1 varchar(100), @Message varchar(200)
-- Check for long running transaction the results.
if exists(select * from #OpenTranStatus)
BEGIN
-- replication related open transaction(s).
if (select count(*) from #OpenTranStatus) = 2
BEGIN
if ((select DATEPART ( hh , getdate())) <> 12 or (select DATEPART ( mi , getdate())) <> 0) return
declare @dname varchar(100)
select @dname = db_name()
print 'dbname : ' + @dname
set @body1 = 'Server :'+@@servername+ ' Replication Transaction(s) exists- check it'
set @Message = ' Running Replication Transaction in database : ' + @dname + CHAR(13)
EXEC msdb.dbo.sp_send_dbmail
@recipients='operator@notification.ca', -- operator email address here
@body = @Message,
@body_format = 'Text'
return 1
END
SELECT @TROUBLE_SPID = CAST(Details AS int) from #OpenTranStatus where ID = 1
SELECT @TRANSACTION_START_TIME = cast(Details as datetime)from #OpenTranStatus where ID = 5
if DATEDIFF(ss,@TRANSACTION_START_TIME, GETDATE()) > @OpenTransactionTreshold
BEGIN
if OBJECT_ID('tempdb..##spwho') > 0 drop table ##spwho
create table ##spwho (
SPID int not null
, Status varchar (255) not null
, Login varchar (255) not null
, HostName varchar (255) not null
, BlkBy varchar(10) not null
, DBName varchar (255) null
, Command varchar (255) not null
, CPUTime int not null
, DiskIO int not null
, LastBatch varchar (255) not null
, ProgramName varchar (255) null
, SPID1 int not null
, requestid int
)
insert into ##spwho EXEC sp_who2 @TROUBLE_SPID
select * from ##spwho
set @body1 = 'Server :'+@@servername+ ' Long Running Transaction(s) with Start Time = '+ cast(@TRANSACTION_START_TIME as varchar(20)) + ' - check it'
set @Message = 'Details of Long Running Transaction' + CHAR(13)
EXEC msdb.dbo.sp_send_dbmail
@recipients='operator@notification.ca', -- operator email address here
@profile_name = 'emailprofilename', -- profile email here
@subject = @body1,
@body = @Message,
@body_format = 'Text',
@query = 'select cast(SPID as varchar(4)) as SPID,LEFT(Status,10) as STATUS,LEFT(Login,15) as LOGIN,LEFT(HostName,15) as HostName,LEFT(BlkBy,5) as BlkBy,LEFT(DBName,15) as DBName,LEFT(Command,20) as Command ,cast(CPUTime as varchar(7)) as CPUTime,cast(DiskIO as varchar(6)) as DiskIO,LEFT(LastBatch,15) as LastBatch,LEFT(ProgramName,30) ProgramNmame from ##spwho',
@attach_query_result_as_file = 0
END
END
--DROP TABLEs
if OBJECT_ID('tempdb..##spwho') > 0 drop table ##spwho
DROP TABLE #OpenTranStatus
go
-- create procedure as system store procedure
EXEC sys.sp_MS_marksystemobject sp_DetectLongRunningTransactions
GO
2. Create SQL jobs to run Monitoring procedure - code below
USE [msdb]
GO
/****** Object: Job [DETECT Long Running TRANSACTIONS] Script Date: 09/26/2014 10:32:25 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/26/2014 10:32:25 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DETECT Long Running TRANSACTIONS',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [1] Script Date: 09/26/2014 10:32:26 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @Counter int
declare @IDCounter int
declare @DBN sysname
declare @Cmd varchar(512)
set nocount on
if OBJECT_ID(''tempdb..#dbname'') > 0 drop table #dbname
select name into #dbname from master.dbo.sysdatabases
alter table #dbname add ID int null
set @IDCounter = 0
update #dbname set @IDCounter = ID = @IDCounter + 1
set @Counter = 1
select @IDCounter = MAX(ID) from #dbname
select @DBN = [Name] from #dbname where ID = @Counter
while @Counter <= @IDCounter
BEGIN
set @Cmd = ''''
set @Cmd = @Cmd + ''use '' + @DBN + '' exec sp_DetectLongRunningTransactions 900''
exec (@Cmd)
set @Counter = @Counter + 1
select @DBN = [Name] from #dbname where ID = @Counter
END
',
@database_name=N'master',
@flags=12
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Long Running Transactions',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20101122,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Procedure sp_DetectLongRunningTransactions takes as parameter @OpenTransactionTreshold which represent treshold in seconds.
It works as follow:
- if there is an open transaction, procedure will determine its duration as of the time when monitoring procedure is executed. When transaction is open longer then specify treshold then notification is send to operator with the transaction details.
- if there is open transaction however its duration is less then treshold notification is not send.
Please make sure that you schedule SQL job frequency executing Monitoring procedure in relation to specify treshold, so for example if specify treshold is 900 sec (15 min) execution frequency should be equal or greater then 15 min.
September 26, 2014 at 8:57 am
Hi Grasshopper,
Yes. We can do this. But only issue I found was scheduling job every now and then. Which may put extra (may not be more) pressure on server.
By using ALERTS we can trigger them when the long running transaction appears. I feel it will be more of real time approach. Please let me know your take on this.
September 26, 2014 at 10:01 am
without scheduling the job..hoe can i get alert
September 26, 2014 at 10:07 am
Job could be connected to SQL Alert or schedule to run.
September 30, 2014 at 10:34 am
Thank you Krystian, I'm testing out your script. When no queries are running it completes successfully, when I kick off a test query and then run it I get this:
Msg 241, Level 16, State 1, Procedure sp_DetectLongRunningTransactions, Line 48
Conversion failed when converting date and/or time from character string.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply