0

I'm looking for a way to send an alert when a DBA tool is connected for "too long" on a SQL Server Production database.

For most of the tools I listed out, I can find the program name with the following query.

select 
    login_time,
    host_name,
    program_name,
    login_name,
    nt_domain,
    nt_user_name,
    last_request_start_time 
 From sys.dm_exec_sessions
 where host_name is not null 

However, I can't find the Activity Monitor tool even though it's running. I use a SQL Server 2012 Management Studio and I run it against a SQL Server 2012 instance.

Do you have an idea about the reason I can't see it ? Does the Activity Monitor do "connect/disconnect" to show its content ?

If it's the case, it's absolutely normal I can't find it there and I would have to change my way to check it with an extended event or a logon trigger...

1

The Activity Monitor shows up as a standard user session, "sleeping" in tempdb (database_id = 2, generally), with the program_name of "Microsoft SQL Server Management Studio".

It only queries at the rate of it's refresh window, but those identifiers, plus the total_elapsed_time, should make it feasible to find your idling monitors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.