SQL SERVER – Script to Kill All Inactive Sessions – Kill Sleeping Sessions from sp_who2

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.

SQL SERVER - Script to Kill All Inactive Sessions - Kill Sleeping Sessions from sp_who2 killall

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)

, , , ,
Previous Post
SQL SERVER – Global Variable @@DEF_SORTORDER_ID – Old and May be Deprecated
Next Post
SQL SERVER – Get Last Known Actual Execution Plan for a Previously Cached Query Plan

Related Posts

5 Comments. Leave new

  • albertvanbiljon
    May 6, 2019 9:42 pm

    Thank you for the post.
    You say that the unclosed connections were causing performance issues – how was this measured? Were the connections taking up memory which could have been used for something else, and if so, how can one see this?
    I’m evaluating Solarwinds DPA and can’t see how one would see how much memory is used for managing user connections and sessions – do you know whether it records this?

    Reply
    • Solarwinds DPA is a great product. Let me fire up my own instance and do the test and will get back to you.

      Reply
  • Great idea of killing the connections which are not closed or in sleep mode for a while. Instead of killing the connections, I would recommend using the connection pooling and limit to max concurrent users for the application. So that DB connection time will be saved and reduces overall time required for your request.

    Pinal, what do you say about my suggestion?

    Reply
  • AHMED ALI ELAGOUZ
    October 6, 2019 12:03 pm

    i face locks alot of time on RDS connection broker database that is hosted in sql failover cluster instance which limited on ram this locks happened in rush hours if i kill the locks process it happend again until i offline and online the service how to prevent this forever

    Reply
  • Hello, Thanks for the script it works fine but the sessions are coming back as sleeping in less than 2 minutes … what should I do to prevent them to come back at least so fast so I could run the processes needing to be alone…
    Thanks,
    Dom

    Reply

Leave a Reply

Menu