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

  • 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?

  • 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
  • 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

  • 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;

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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[/url]
    Learn Extended Events

  • good one,,

    cheers !

  • IS that code killing queries that are blocking and running over 2 hours?

  • 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.

  • 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
  • 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...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

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