Kill all Orphan Processes on a SQL Server
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 22 Aug 2012
This is a variation on the usp_KillProcesses stored procedure.
The kill criteria now is all user processes on the SQL Server whose last_batch time is longer ago than the supplied number of hours (default 24 hours).
This is extremely useful for cleaning up after messy 3rd party applications and I have used it very effectively to manage connection levels on many production servers in the past. Not ideal, but a necessary evil when you don't have access to tighten up the connection handling code. Usually, it gets scheduled in the quiet hours and runs sub-second even for several thousand connections.