Sleeping Sessions From Last 30 mins In Sql Server


           Below query helps us to find out if any connection is in sleeping state in last 30 mins. This can be use full for server which are always having memory pressure and releasing few connections can improve conditions.

/************************************/
select
s.spid,t.text, s.last_batch,
 s.login_time, s.open_tran,
 s.nt_username, s.hostname,s.status
from
sys.sysprocesses s cross apply
 sys.dm_exec_sql_text(sql_handle) t
where
s.status not in ( 'runnable', 'suspended' , 'running')
 AND datediff (mi,last_batch, getdate()) > 120 -- inactive for 30 mins
 and  not(nt_username like 'serviceaccount%') -- do not kill service account
 and  not(nt_username like 'admins%') -- do not kill admin accounts
 and  s.spid <=50 -- do not kill SQL system generated spids
/************************************/


No comments:

Post a Comment