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…
Best Answer
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.