An inactive or sleeping session may still be a useful pooled connection. A customer case prompted my earlier session-cleanup script.
-- Read-only candidate inventory, not a kill loop.
SELECT session_id, status, login_name, host_name, program_name,
last_request_end_time, open_transaction_count
FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND session_id <> @@SPID
AND status = N'sleeping'
AND last_request_end_time < DATEADD(hour, -24, SYSDATETIME())
ORDER BY last_request_end_time;
-- Revalidate identity, activity, transaction and ownership immediately before
-- an explicitly approved KILL of one selected session. IDs can be reused.The third-party application appeared to leave connections open, and we raised a vendor ticket. My old cursor killed sleeping sessions after 24 hours. Pools commonly keep useful idle connections. Sleeping sessions can also own open transactions.
Killing them can disrupt applications and cause lengthy rollbacks. Start with session, request, transaction, blocking and connection inventories. The elapsed cutoff here avoids counting hour boundaries. Results remain a snapshot; a session can become active or its ID be reused.
An approved temporary termination needs an immediate identity check and a recorded reason. Follow rollback progress afterward. Correct connection lifecycle or pooling behavior with the vendor instead of scheduling blanket termination.
The health check included a plan for the underlying issue. It did not establish that every idle connection consumes a worker. Nor did it show that idle connections universally cause poor performance.
Related reading
A sleeping session is not automatically an abandoned connection, it is a state to correlate with application ownership.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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 …