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.
Thanks a lot for this post!!! you've solved a problem with my Sql-Server!
ReplyDeleteHappy to hear that :)
Deletegetting error: Msg 4104, Level 16, State 1, Line 62
ReplyDeleteThe multi-part identifier "B.SPID" could not be bound.
So How do I then Kill the log block or is that what the EXEC(@QKILLsp) accomplishes?
ReplyDeleteExactly , EXEC statement will kill the culprit session.
DeleteI am unable to see the script
Deletei was unable to see the script
DeleteGreat, solve many problems
ReplyDeleteGReat. tks a lot
ReplyDeletegreat script ...!! so it does detection of block-by and kills it ..!! right ?
ReplyDeleteHi Aasim,
ReplyDeleteGrate script!!works perfectly on sql 2k5 and 2k8R2 but on 2012 the job it self is appearing in long running report report. Any suggestions?
Where is the link to the Script , Siva
ReplyDeletethanks a lot
ReplyDeleteHi, aasim abdullah
ReplyDeleteThe 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
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