Get list of current active sessions and info

Question

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.

Answer 1

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