CODEWITHSUNDEEP
sqlsql-serversql-server-2008alert
Paul
Paul

Reputation: 777

Alert for slow running query on SQL server

I would like to create a SQL job that runs a query and if it takes over a set amount of time to send me an email alert. Any ideas how to do this. I am running SQL Server 2008.

Upvotes: 0

Views: 1474

Answers (2)

nathan_jr
nathan_jr

Reputation: 9292

To measure the run time of a specific query you could simply capture it within the job itself and then either raise an event to trigger an Agent failure alert or send the email yourself in the t-sql using dbmail:

declare @Elapsed int,
        @Start datetime = getdate();

--your query
waitfor delay '00:00:03'
--

select @Elapsed = datediff(ss, @Start, getdate());

raiserror('Query ran for %d sec(s)', 10, 1, @Elapsed) with nowait;

Downside of this approach is you have to actually complete the query to measure the runtime.

Upvotes: 2

Nir Alfasi
Nir Alfasi

Reputation: 53535

You can install Nagios and write a test that will check the running time of a query and if the test fails - send you an email. We used to do the same on my company, now we're using another (expensive) tool called Event247

Upvotes: 0

Related Questions