Version 3

    DESCRIPTION

    This alert is for SQL Server instances only and will tell you when SQL statements have executed more than a defined amount of time. This example uses a 300 second threshold, meaning any SQL statements that have been executing for longer than 300 seconds (5 minutes) will be alerted. The alert query executes against the monitored database directly so caution should be taken on how often the alert is executed, especially if less than the default of every 300 seconds.

     

    ALERT DEFINITION

    To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:

     

    Alert Name: SQL Server - Long Running Queries

     

    Execution Interval: 5 Minutes

     

    Notification Text: The following list of SQL statements have been running for over 5 minutes.

     

    SQL Statement:

     

    SELECT

    'SPID='+CONVERT(VARCHAR,a.session_id)+' has been running the following for '+CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))+' seconds: '+convert(varchar,b.text), CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))

    FROM sys.dm_exec_requests a

    CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b

    WHERE a.status <> 'background'

    AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 300 --This value of 300 must match the Execution Interval set in the alert

     

    Execute Against: Monitored Database

     

    Units: Seconds

     

    High Threshold: MIN 300 (This value must match value found in query. Default: 300 ), MAX empty