Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Possible to use tored proc to kill all queries running longer than 2 hours AND are blocking other...


Possible to use tored proc to kill all queries running longer than 2 hours AND are blocking other queries

Author
Message
dndaughtery
dndaughtery
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 1075
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55211 Visits: 44696
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

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


dndaughtery
dndaughtery
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 1075
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
Neeraj Dwivedi
Neeraj Dwivedi
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 1323
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;


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52542 Visits: 40334
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22952 Visits: 18262
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

katukuri.sqldba
katukuri.sqldba
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 111
good one,,

cheers !
dndaughtery
dndaughtery
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 1075
IS that code killing queries that are blocking and running over 2 hours?
Neeraj Dwivedi
Neeraj Dwivedi
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 1323
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55211 Visits: 44696
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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search