0

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.

Nir Alfasi
  • 53,191
  • 11
  • 86
  • 129
Paul
  • 757
  • 3
  • 10
  • 38
  • Seem like a duplicate. http://stackoverflow.com/questions/577233/sql-alert-when-stored-procedure-executes-for-too-long – sam yi Jul 10 '12 at 04:18
  • @Paul do you want to trial a specific query or are you trying to find *any* slow running query? – nathan_jr Jul 10 '12 at 05:00
  • I want to use it on a specific query – Paul Jul 10 '12 at 05:48
  • 1
    Seems like it would put more strain on the server - thus reducing the amount of useful work it can do (since you're apparently discarding the results of an expensive to compute query, so long as its fast enough) – Damien_The_Unbeliever Jul 10 '12 at 07:09

2 Answers2

2

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.

nathan_jr
  • 9,092
  • 3
  • 40
  • 55
  • That's exactly the upside of using Nagios - you don't have to wait for the query to return! – Nir Alfasi Jul 10 '12 at 07:15
  • Or you could have 2 jobs. One starts the other job has a `waitfor delay '00:00:03'` then checks if the other job is running and if so kills it (optionally) and sends the alert. Wonder if service broker has some sort of timeout too. – Martin Smith Jul 10 '12 at 07:58
0

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

Nir Alfasi
  • 53,191
  • 11
  • 86
  • 129
  • I was looking for something I could do on the SQL server. – Paul Jul 10 '12 at 06:03
  • according to the link @sam yi posted above, there's no built-in solution for MySql server. But, if you don't want to use external tools, it shouldn't be difficult to take one of the procedures posted on that link and create a php code that will run on scheduled basis (or just run in an infinite loop and "sleep" for a few minutes in between), which will call that "check"-procedure and will email you if necessary. – Nir Alfasi Jul 10 '12 at 06:31