May 3, 2013 at 1:36 pm
Is it possible to use a stored procedure to kill all queries running longer than 2 hours AND are blocking? Couls someone help me figure out how to do this?
May 3, 2013 at 1:54 pm
Possible, yes. Whether it's a good idea or not is another matter.
What happens if that's a long-running data import. Job automatically kills it after 2 hours, job rolls back for the next maybe 3 hours, net result several wasted hours and a data import that needs to be rerun and when it is, it'll be killed after 2 hours, etc, etc.
Better idea, identify long running queries that cause blocking and tune them so that they aren't so long running and don't cause so much blocking. Fix the cause, don't patch the symptoms.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2013 at 3:00 pm
I agree but in this case its with an adhoc dev enviroment which its ok. If someone could explain to me how to go about it I would appreciate it
May 4, 2013 at 8:12 pm
I totally Agree with Gail on that it will be total waste of resource if you do such kind of stuff. But if you really want to do that, you can use something like below.
--
Create PROC KillLongRunningQuery(
@DBID int,
@WaitTime bigint)
as
--exec KillLongRunningQuery 5,20000000
DECLARE scursor CURSOR FAST_FORWARD FOR
SELECT SPID
FROM MASTER..sysprocesses WHERE waittime> @WaitTime--Specify time in
and DBID = @DBID -- pass your DBID
and spid > 50
ORDER BY SPID
DECLARE @SPID int
OPEN scursor;
FETCH NEXT FROM scursor INTO @SPID
WHILE @@fetch_status = 0
BEGIN
DECLARE @StrSQL as varchar(200)
SET @StrSQL= 'Kill ' + convert (char(4),@SPID )
EXEC ( @StrSQL)
FETCH NEXT FROM scursor INTO @SPID
END
CLOSE scursor;
DEALLOCATE scursor;
May 5, 2013 at 11:16 am
dndaughtery (5/3/2013)
I agree but in this case its with an adhoc dev enviroment which its ok. If someone could explain to me how to go about it I would appreciate it
As you've seen, this can certainly be done but it's a really, really bad idea on several fronts. The best thing to do is to find the person who's actually running the query and have them cancel the query. The reason for that is if you kill the spid, it may get "stuck" in a never ending- CPU consuming "zero percent to go" rollback. This is a documented problem with SQL Server and the only way to kill the spid with the bad rollback is to bounce the SQL Server service.
The other thing is a bit more on the human side. If you kill the run, the person who wrote it learns nothing and may just try to run it again... and again... and again. What you need to do is go see the "user", show THEM how to kill the run, and then help them improve their query and, perhaps, how to read an execution plan to help avoid such problems in the future in a thoughtful mentor-like fashion.
If neither you or the user have time to do that, then consider how much time each of you waste during such episodes. You work for the same company. Take the time to help each other out. Then the "user" will pass his/her knowledge on to the next person and suddenly your job gets a whole lot easier because you don't have to spend so much time killing spids. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2013 at 11:20 am
Jeff Moden (5/5/2013)
dndaughtery (5/3/2013)
I agree but in this case its with an adhoc dev enviroment which its ok. If someone could explain to me how to go about it I would appreciate itAs you've seen, this can certainly be done but it's a really, really bad idea on several fronts. The best thing to do is to find the person who's actually running the query and have them cancel the query. The reason for that is if you kill the spid, it may get "stuck" in a never ending- CPU consuming "zero percent to go" rollback. This is a documented problem with SQL Server and the only way to kill the spid with the bad rollback is to bounce the SQL Server service.
The other thing is a bit more on the human side. If you kill the run, the person who wrote it learns nothing and may just try to run it again... and again... and again. What you need to do is go see the "user", show THEM how to kill the run, and then help them improve their query and, perhaps, how to read an execution plan to help avoid such problems in the future in a thoughtful mentor-like fashion.
If neither you or the user have time to do that, then consider how much time each of you waste during such episodes. You work for the same company. Take the time to help each other out. Then the "user" will pass his/her knowledge on to the next person and suddenly your job gets a whole lot easier because you don't have to spend so much time killing spids. 😉
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2013 at 7:05 am
good one,,
cheers !
May 7, 2013 at 4:40 pm
IS that code killing queries that are blocking and running over 2 hours?
May 7, 2013 at 5:00 pm
Have you even tried it? If yes, what values you have passed? Waittime is value passes in millisecond so you have to convert hours in ms.
May 8, 2013 at 8:18 am
Can you be a bit clearer what you want?
Kill all the queries that have been running for 2 hours and have been blocking other queries for 2 hours?
Kill all the queries that have been running for 2 hours and have blocked some queries at some point in that 2 hour period?
Kill all the queries that have been running for 2 hours and are blocking other queries at the point that the query is checked?
Something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 8, 2013 at 8:37 am
GilaMonster (5/8/2013)
Can you be a bit clearer what you want?Kill all the queries that have been running for 2 hours and have been blocking other queries for 2 hours?
Kill all the queries that have been running for 2 hours and have blocked some queries at some point in that 2 hour period?
Kill all the queries that have been running for 2 hours and are blocking other queries at the point that the query is checked?
Something else?
Kill all queries which has digit '2' in it ...
The ones which have no digit 'two' better to be killed too!
:hehe:
I guess, OP should consider Jeff Moden suggestion...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply