Wednesday, September 5, 2012

SQL Server: Automatically Detect and Kill Long Running Sessions on Production Servers


Problem:  On production servers, how to detect long running sessions and kill them automatically if they are exceeding a specific amount of time.
Solution:
Solution to this problem is very simple. Just create a job, which will detect long running sessions by running query against sys.sysprocesses executing SP_WHO or sp_WHO2 and then kill those sessions which are exceeding a time limit. BUT major problem is that SP_WHO or SP_WHO2 are unable to provide important information, like actually which execution command, stored procedure call or tsql batch is being executed by this culprit session? To avoid, in future and to find out permanent solution for such long running quires we need to mail them before we kill these sessions.
Following is the script we like to use on production servers, to find out costly sessions and send complete information to DBA team through mail, before killing these costly processes, automatically.



Mail output would be as following.

15 comments:

  1. Thanks a lot for this post!!! you've solved a problem with my Sql-Server!

    ReplyDelete
  2. getting error: Msg 4104, Level 16, State 1, Line 62
    The multi-part identifier "B.SPID" could not be bound.

    ReplyDelete
  3. So How do I then Kill the log block or is that what the EXEC(@QKILLsp) accomplishes?

    ReplyDelete
  4. great script ...!! so it does detection of block-by and kills it ..!! right ?

    ReplyDelete
  5. Hi Aasim,
    Grate script!!works perfectly on sql 2k5 and 2k8R2 but on 2012 the job it self is appearing in long running report report. Any suggestions?

    ReplyDelete
  6. Where is the link to the Script , Siva

    ReplyDelete
  7. Hi, aasim abdullah
    The Subject should be @subject = 'Long Running Session Detected' instead of 'Blocking Session Detected'.
    This is a good query which finds not only Blocking, but Long Running Queries, and Sleeping Connections.
    Currently the header is causing confusion amount database developers on the team

    ReplyDelete
  8. Thank you for posting above query. This query has one flaw - if I am running two update scripts in different window and didn't commit them, above script will still capture those sessions whereas they are not blocking each other. It is basically a query to check long running processes and not blocking.

    ReplyDelete

All suggestions are welcome