Get list of current active sessions and info

techresearch7777777 1,801 Reputation points
2021-08-11T22:27:49.627+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,899 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Cris Zhan-MSFT 6,611 Reputation points
    2021-08-12T02:57:44.723+00:00

    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
    
    0 comments No comments