How to stop the execution of the current query on SQL Server!
Let's say I have several hundred queries running on my sql server. Now I want to protect my server from bad requests by automatically stopping these requests. How can I achieve this behavior in SQL Server 2005 without using the profiler and the KILL command?
Thank you, Salman Shehbaz.
SQLServer has a query execution timeout which will issue a query if it is not allocated within a certain amount of time, depending on the estimated cost in the execution plan (it waits 25 times until it is estimated by default). If the request expires frequently, it needs optimization or more memory is required.
If you're looking for already running queries that are taking longer than expected, the only real way is to use a profiler and determine what the problem is. It is not a good practice to kill queries if they are busy with the production system for too long.
This may provide some help
a source to share
create a new stored procedure based on something like:
sp_lock
sp_who2
beta_lockinfo (http://www.sommarskog.se/sqlutil/beta_lockinfo.html)
Just copy one of these stored procedures and edit it to find "bad query" and then type KILL on spid. Create a job to run every N minutes and run this stored procedure.
Warning. Your job may turn out to be a "bad request" and kill yourself. This approach will waste system resources, but if you really want to, try this method!
a source to share