November 4, 2020 at 3:11 pm
I am in a middle of removing a server which is part of AG and someone has asked me to see if there are users, connecting to that specific server using a listener name so I used a DMV to capture all that information. The script is below
SELECT es.login_name
,es.program_name
,login_time
,ec.client_net_address
,ec.client_tcp_port
,agl.dns_name
,aglip.ip_address
,agl.port
FROM sys.availability_group_listeners agl
INNER JOIN sys.availability_group_listener_ip_addresses aglip ON agl.listener_id = aglip.listener_id
INNER JOIN sys.dm_exec_connections ec ON ec.local_net_address = aglip.ip_address
INNER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
where login_time >= DATEDIFF(day, 7, getdate())
I ran the script on the server and saw no records. (also, for how long this DMV sys.dm_exec_connections hold the data for? cause when I changed the where clause to 14 days, it only showed me the data for 9 days. On other servers, it showed me data for 13 days etc.)
For test, I logged onto that server (which I am going to decommission) using a listener name, reran my query again and I saw myself. Which tells me that no one has been using that server, using that specific listener.
Question: Is there a way to capture connections using extended events or any other method just to re-confirm that no one is connected to the server, using that listener name?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
November 5, 2020 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 6, 2020 at 1:44 pm
The connections you see are the ones that are connected to the instance, there's no history, SQL Server doesn't keep one.
Session_Id> 50 are user sessions these are the ones you should be aiming for.
If you want to capture this information, simple and easy would be to create a table and run a job using that query.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply