Sql-server – Custom database alert – Can it be done

sql server

I have this neat little query which let me know any ongoing query that stuck running for more than 3 minutes. 

SELECT 
sqltext.TEXT,
s.session_id, r.status,
r.total_elapsed_time, r.cpu_time, r.wait_time
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id 
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE s.is_user_process = 1 
AND s.session_id <> @@SPID AND r.total_elapsed_time > 300000000

I came across this article that show how to set up SQL agent alert and send as notification. Can I combine with my query so it sends alert email when I have query running longer than 3 minutes?

Best Answer

One easy way to do this is to create a SQL Agent job with a step that runs the following code:

<Your SELECT statement>
IF (@@ROWCOUNT > 0)
    BEGIN
        RAISERROR('Long-running query detected!', 11, 1 )
    END

Configure notification on the job to send an email on failure, and if any rows are returned by the query, you'll get an email that the job failed.

Instead of setting up the job failure notification, you could set up a SQL Agent alert on the error that is raised as in the article you referenced. But if the job notification is adequate it's less work to set up.

Related Question