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


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

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

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

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


All suggestions are welcome