I consider myself fortunate that I get to work with so many different clients while engaged in Comprehensive Database Performance Health Check. Recently we found out that one of the third party application for the client is not closing the connections which they open after completing the transactions. This was negatively affecting their performance. We immediately opened the ticket with the third party application and while they provide the solution after patching the application we decided to kill all the inactive sessions.
Here is the script to kill all inactive sessions. People those who usually ask for the script to kill sleeping sessions from sp_who2 can also use this script.
DECLARE @user_spid INT DECLARE CurSPID CURSOR FAST_FORWARD FOR SELECT SPID FROM master.dbo.sysprocesses (NOLOCK) WHERE spid>50 -- avoid system threads AND status='sleeping' -- only sleeping threads AND DATEDIFF(HOUR,last_batch,GETDATE())>=24 -- thread sleeping for 24 hours AND spid<>@@spid -- ignore current spid OPEN CurSPID FETCH NEXT FROM CurSPID INTO @user_spid WHILE (@@FETCH_STATUS=0) BEGIN PRINT 'Killing '+CONVERT(VARCHAR,@user_spid) EXEC('KILL '+@user_spid) FETCH NEXT FROM CurSPID INTO @user_spid END CLOSE CurSPID DEALLOCATE CurSPID GO
I am very sure the script I provided can be further improved. If you have a better script I request you to post it here so I can share the same with everyone with due credit to you.
Here is a brief note about the Comprehensive Database Performance Health Check. It is my MOST popular service. Customers opt for this service when they face performance issues with SQL Server. Based on the complexity of your system, it can take anywhere from 2–4 hours to complete the entire exercise.
During this exercise, we identify your performance problems and organize an action plan to resolve them. Most of the time we will get to the part where we start implementing fixes within the first 75 minutes.
Reference: Pinal Dave (https://blog.sqlauthority.com)