Post reply

How to write a script that kills a process if it's blocking over 1 minute

  • January 19, 2024 at 1:14 am

    #4364142

    Please enlighten me on how to write a script that kills an event if it's blocking another process over 1 minute.

    In theory I would run this script via SQL server agent, every 10 minutes, for 24 hours.

     

    Thank you

  • January 19, 2024 at 2:54 pm

    #4364474

    My opinion - I wouldn't do this. The reason, lets say this scenario comes up:

    User A is doing a massive update on a table and it will take 35 minutes to complete and 30 minutes have passed.

    User B tries to do a SELECT on the same table and is blocked.

    At the 31 minute mark, you are killing the BLOCKER which has been running for 31 minutes for the update. I find that this is often takes just as long to roll back as the duration that has passed, so you MAY be waiting 31 minutes for that SPID to roll back even though it was only going to be another 4 minutes for it to complete.

    Just as an example. A 30 minute update is HUGE and doesn't happen often, but just as an example.

    Are you SURE you want a job that will kill the blocker IF it blocks for 1 minute or more?

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • January 19, 2024 at 2:58 pm

    #4364476

    This isn't the right way to do things.  You can easily get to the point of where your system is doing nothing but rollbacks.  You could also be causing grave damage to systems/data.

    Instead, do the right thing and have your system alert you as to what is doing the blocking and what it's affecting and then help people fix the code.

    If your request for automated-kill-code is due to the result of trying the above and no one listening, then you need to take it up the chain of command.

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

Viewing 3 posts - 1 through 2 (of 2 total)

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