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.

No comments:

Post a Comment

All suggestions are welcome