Monitoring Longest Running Transaction using SQL Server Agent Alerts

  • Comments posted to this topic are about the item Monitoring Longest Running Transaction using SQL Server Agent Alerts

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

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

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

  • 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'

  • 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

  • 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?

  • 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

  • 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

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

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

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

  • without scheduling the job..hoe can i get alert

  • Job could be connected to SQL Alert or schedule to run.

  • 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