question

techresearch7777777-7743 avatar image
0 Votes"
techresearch7777777-7743 asked Criszhan-msft answered

Get list of current active sessions and info

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

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





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.