How To Detect Active User Session by sqlcmd

Question

I'd like to get active user session count before rebooting instance, exclude any background sessions.

Just thinking to use following query but I'm not sure how to exclude select session for tempdb by SQL Server.

SELECT COUNT(*) FROM sys.dm_exec_requests WHERE session_id in (select session_id from sys.dm_exec_sessions where status = 'running')

Any advice would be appreciated again.

score 0 · Answer 1 · answered Jul 03 '18 at 07:36

If you need only active user sessions and exclude sessions for tempdb

Try this

select count(*) FROM sys.dm_exec_requests 
where 
    database_id <> DB_ID('tempdb') 
    and 
    session_id in 
        (select session_id from sys.dm_exec_sessions where is_user_process = 1 and status='running')

score 0 · Answer 2 · answered Jul 03 '18 at 08:29

Many thanks for your prompt reply, Dmitry:) I could achive it as follows;

set StrSQL="SET NoCount ON;SELECT COUNT(*) FROM sys.dm_exec_requests WHERE database_id <> DB_ID('tempdb') and session_id in (select session_id from sys.dm_exec_sessions where is_user_process = 1 and status='running' and session_id <> @@spid)"
for /F %%A in ('sqlcmd -Q%StrSQL% %CONN_PARAM%') do set USER_SESSION_CNT=%%A
echo Current User Session Cnt：%USER_SESSION_CNT%

if %USER_SESSION_CNT% gtr 0 goto WARNING_USER_SESSION

Then, Skip the reboot task. Thank you so much for all of your support.

How To Detect Active User Session by sqlcmd

2 Answers2