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:
Write commentsPlease do not enter spam links