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)
9 Comments. Leave new
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?
Solarwinds DPA is a great product. Let me fire up my own instance and do the test and will get back to you.
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?
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
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
I’ve been doing this on my production SQL servers for some time. I have an Agent job that runs periodically and executes this proc in the master database:
create or alter procedure KillSleepingSpids
@AgeInHours int=12
as
set noCount on
declare @spid int
select spid into #spids
from sys.sysprocesses
where spid>50
and [dbid]>4
and [status]=’sleeping’
and DateDiff(hh,last_batch,GetDate()) > @AgeInHours
while exists (select * from #spids)
begin
select top 1 @spid=spid from #spids
delete from #spids where spid=@spid
exec(‘kill ‘ + @spid)
end
drop table #spids
go
I have used the script, but it does not kill the sleeping items. they are still there when I re-run sp_who2
Check your WHERE condition.
Sir, Thank you very for sharing the code sir this is very useful for us …
at the same time we need to Kill the suspended sps which are not in runnable status can you please modify it …