Categories:

List of currently active sessions for each database on your servers.


Please note that you need VIEW SERVER STATE permission to view all the sessions. Otherwise, you’ll see only your own.


--- Currently Active Sessions
Select
s.spid as 'pid'
,s.blocked as 'Blocks'
,RTRIM(CAST(s.hostname as nvarchar(256))) as 'FromServer'
,RTRIM(CAST(s.loginame as nvarchar(256))) as 'UserName'
,RTRIM(CAST(d.name as nvarchar(256))) as 'DatabaseName'
,RTRIM(REPLACE(s.lastwaittype,'RESERVED_MEMORY_ALLOCATION_EXT  ','RES_MEM_ALLOC_EXT')) as 'WaitType'
,s.waittime / 1000 as 'WaitTime' -- wait time in seconds
,RTRIM(CAST(s.cmd as nvarchar(32))) as 'Statement'
,s.status as 'Status'
,c.num_reads as 'PageReads' -- number of packet reads
,c.num_writes as 'PageWrites' -- number of packet writes
,RTRIM(CAST(s.program_name as nvarchar(256))) as 'ProgramName'
,t.text as 'ExecutedQuery'
,datediff(minute,s.last_batch, GETDATE()) as 'Duration' -- execution time in munites
--,EQP.query_plan as 'execution plan'
-- r.percent_complete
--into #tmp
from sys.sysprocesses s
join sys.databases d on d.database_id = s.dbid
left join sys.dm_exec_requests r on r.session_id = s.spid
cross apply sys.dm_exec_sql_text (s.sql_handle) t
--CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS EQP
inner join sys.dm_exec_connections c
on c.session_id = s.spid
where (s.cmd not like '%AWAITING%' or s.status = 'suspended')
and t.text not like '%--- Currently Active Sessions Select%'
order by duration desc

Tags:

2 Responses

  1. I was extremely pleased to discover this great site. I want to to thank you for ones time just for this wonderful read!! I definitely liked every little bit of it and i also have you bookmarked to look at new information in your blog.

Leave a Reply

Your email address will not be published. Required fields are marked *