Get list of current active sessions and info

Question

Get list of current active sessions and info

techresearch7777777 1,981

Hello, am aiming to set up a SQL 2016 Agent job that runs say every 15 mins and would like to email me if the Sessions count is greater > 10 where each Sessions have statuses IN ('RUNNING', 'RUNNABLE', 'SUSPENDED') and provide at least the following info columns:

Session ID #
Login name
Host name
DB name
Last full SQL command
Login date-time
Last batch date-time
Session Status - like mentioned above mainly interested IN ('RUNNING', 'RUNNABLE', 'SUSPENDED') where total count of the 3 are greater > than 10...so far believe I'm not concerned of SLEEPING statuses.

Can anyone provide me SQL script or point me in direction to do this please?

Thanks in advance.

1 answer

Your answer

Answer 1

Cris Zhan-MSFT 6,661

Hi,

Please check this:

select 
SDES.session_id,SDES.login_name,
SDES.login_time,SDES.host_name,
SDES.status,SDES.last_request_end_time,
DEST.TEXT  
from sys.dm_exec_sessions SDES 
inner join sys.dm_exec_connections SDEC on SDES.session_id=SDEC.session_id  
CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST 
where SDES.status in ('RUNNING', 'RUNNABLE', 'SUSPENDED')

if (select count(*) from (above query) as t) >10
begin
--send email
end

Balbeer Yadav 0

SELECT 
    r.session_id,
    r.start_time,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    r.reads,
    r.writes,
    s.login_name,
    s.host_name,
    s.program_name,
    t.text AS query_text
FROM 
    sys.dm_exec_requests r
JOIN 
    sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE 
    r.session_id != @@SPID;

Share via

Get list of current active sessions and info

1 answer

Your answer