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

/************************************/