SQL Server Connections history?

Question

HI I am having SQL Server Replication setup and between A and B servers .

Yesterday by some how i find SQL server lost connections  with one other and got backup after 4-5 hours i am not sure the exact time  and , I saw the lost connection mentioned in EverntViewer/SQL Serevr Logs etc ..but i did not find any information when it came up online,i tried in replication monitor which did not give any info about lost connections

Is there any way we can find in SQL Server or windows ,

1.when we loose connections between two servers and when they are up ??

2.what are existing connections do sql server have  , history ??

Please help me on this

Thanks

Answer 1

HI I am having SQL Server Replication setup and between A and B servers .

Yesterday by some how i find SQL server lost connections  with one other and got backup after 4-5 hours i am not sure the exact time  and , I saw the lost connection mentioned in EverntViewer/SQL Serevr Logs etc ..but i did not find any information when it came up online,i tried in replication monitor which did not give any info about lost connections

Is there any way we can find in SQL Server or windows ,

1.when we loose connections between two servers and when they are up ??

2.what are existing connections do sql server have  , history ??

Please help me on this

Thanks

Answer 2

I'm surprised that you cannot find any details in the Replication Monitor history for the Distribution Agent, as this should be logging details of when connections fail to the Subscriber because the agent itself will fail, that is supposing you are using a typical Transactional Replication Topology with Push Subscriptions.

SQL Server does not maintain a history of connections but for troubleshooting purposes you could roll your own monitoring solution by using DMVs such as sys.dm_exec_sessions and sys.dm_exec_connections.

You can also record successful Login Attempts as well as Failed Logins (on by default) to the SQL Server Error Log by adjusting the server lever security configuration property.

If the actual goal is instead to monitor when there are issues with Replication, rather than server availability, then you can create and use the Latency monitors for this as the thresholds you set will be breached when connectivity issues arise.

---

John Sansom | SQL Server DBA Blog | @SQLBrit on Twitter

Answer 3

sys.dm_exec_sessions and sys.dm_exec_connections. these gave  me only the connection or logon details since morining, how can i find about history for 2 days for example ?

You can also record successful Login Attempts as well as Failed Logins (on by default) to the SQL Server Error Log by adjusting the server lever security configuration property.: Will do this

If the actual goal is instead to monitor when there are issues with Replication, rather than server availability, then you can create and use the Latency monitors for this as the thresholds you set will be breached when connectivity issues arise.: OK Good Point