Stop query execution if it is taking too long

Question

Stop query execution if it is taking too long

MacDaddy

SSC Eights!

Points: 993
More actions
January 14, 2010 at 8:15 am

#137489

Good afternoon everyone,
Is there a way to set a timeout on a query so that if it exceeds a set amount of time it will cease processing and rollback.
I know SQL Server has timeouts built in but I'd like something that I can specify so for instance if a query is known to take 5 seconds to complete normally but it's still crunching away after 5 minutes then it can stop the execution.
Preferably I'd like to be able to write in an if clause that says if the query executes in under X milliseconds then continue to the next process in the batch otherwise halt execution and notify the administrator via sp_send_dbmail.
Thanks in advance,
Paul
Keep the rubber side down and the shiny side up.

Viewing 5 posts - 1 through 4 (of 4 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You could set up a separate process that would run every five minutes, look for long-running queries, and alert you to them. There isn't a way that I know of to do that inside the query itself, unless it's a multi-statement procedure and you check runtime between DML commands. But not within a single DML command.

Would a separate monitor process accomplish what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

MacDaddy SSC Eights! Points: 993 More actions · Answer 2

I hadn't thought about using a seperate monitor process but I reckon that would probably work just as well.

If you have any ideas on how to accomplish this (I'm not exactly a DBA if you know what I mean) I'd be most grateful.

Thanks,

Paul

Keep the rubber side down and the shiny side up.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Take a look at "sys.dm_exec_sessions" in Books Online.

Here's a sample query that might get you started:

select *

from sys.dm_exec_sessions

where status = 'running'

and datediff(minute, last_request_start_time, getdate()) >= 5;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

MacDaddy SSC Eights! Points: 993 More actions · Answer 4

Ah, that's great - thank you very much. 🙂

Paul

Keep the rubber side down and the shiny side up.