Post reply

Alert when long running queries are running

  • July 14, 2011 at 7:58 am

    #241496

    SQL Gurus,

    Is there a way to set up a job which will send an alert when long running queries are running on a sql instance?

    Thanks in advance.

  • July 14, 2011 at 10:54 am

    #1352972

    You could create a job to run every so often to check the total_elapsed_time in sys.dm_exec_requests and send an email if a long running process is found. Something like

    IF EXISTS (SELECT COUNT(s.session_id) FROM sys.dm_exec_sessions s

    INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id

    WHERE r.total_elapsed_time > 10000 AND s.group_id > 1)

    BEGIN

    Mail or whatever goes here

    END

    Of course how many milliseconds that r.total_elapsed_time should be set for and how often to run the job is something you'll have to decide.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply