Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Possible to use tored proc to kill all queries running longer than 2 hours AND are blocking other queries Expand / Collapse
Author
Message
Posted Friday, May 3, 2013 1:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 355, Visits: 801


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?
Post #1449349
Posted Friday, May 3, 2013 1:54 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1449353
Posted Friday, May 3, 2013 3:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 355, Visits: 801
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
Post #1449360
Posted Saturday, May 4, 2013 8:12 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 31, 2014 4:01 PM
Points: 835, Visits: 1,192
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;

Post #1449485
Posted Sunday, May 5, 2013 11:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 35,572, Visits: 32,164
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1449532
Posted Sunday, May 5, 2013 11:20 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 17,964, Visits: 15,967
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 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.


+1




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1449535
Posted Monday, May 6, 2013 7:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:10 AM
Points: 13, Visits: 110
good one,,

cheers !
Post #1449703
Posted Tuesday, May 7, 2013 4:40 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 355, Visits: 801
IS that code killing queries that are blocking and running over 2 hours?
Post #1450359
Posted Tuesday, May 7, 2013 5:00 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 31, 2014 4:01 PM
Points: 835, Visits: 1,192
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.
Post #1450365
Posted Wednesday, May 8, 2013 8:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1450598
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse