How to debug database server freeze (not accepting connections, timeouts etc) Apologies if this question is a little bit vague but we are having issues for a couple of days now and have no clue on what may be wrong.

We have a pair of Windows servers with SQL Server installed in high availability (Always On in a master / replica setup). Exact spec is Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30  Enterprise Edition (64-bit) on Windows Server 2016 Standard 6.3  (Build 14393: )

SQL Server has around 7-8 databases to support different services of a single quite sizeable app. The sizes of each database varies but it ranges from a few GB to over 1TB. Some apps 

Suddenly (check note 1), 2 days ago our master db server instance unexpectedly stopped working, requests started timing out etc. When I say it hangs, I mean that nothing goes through on any database. The whole DB server just stops working. The OS and server is fine though. Its just the SQL Server instance.

When this happens we need to manually fail-over to the replica, restart the SQL Server processes on the old master and re-attach it to the cluster.

This has started happening 2 days ago and it occurs every 8-10 hours or so. I thought there was a regular frequency but there isn't unfortunately. Through this time we tried pausing all maintenance jobs on SQL Server (index rebuilds, log cleanup, backups etc). The problem still occurs.

I know that you dont have a magic ball to guess whats going on. So this question is how would you go about finding the problem here? What commands or steps would you run to get more info? Obviously we can't reproduce the issue - it just occurs.

Btw, I am not a DBA but I dont quite get why SQL Server doesnt automatically failover since nothing works on master...The underlying process is still running but nothing works...

Note 1: No app releases have happened to justify that. No releases at all really. No major change in db sizes or anything along these lines really.
So this question is how would you go about finding the problem here? What commands or steps would you run to get more info? 


Monitoring Tool

Do you have a monitoring tool?  If so, check and see what the wait stats are / were during (or at least leading up to) the incident.

Logging Queries and Waits

If you don't have a monitoring tool, I would recommend setting up SQL Server Agent jobs to log information about wait stats and active queries to a table for later analysis.  There are a number of ways to do this, such as:


How to Log Activity Using sp_whoisactive (for active queries)
How to Log Wait Stats to Table with sp_BlitzFirst (for wait stats)
Wait statistics, or please tell me where it hurts (great overview of your top waits)


DAC

While the problem is occurring, you could connect to SQL Server using the dedicated admin connection, and then run sp_WhoIsActive to see exactly what is running and slowing things down.  Despite not accepting connections from other sources, you should still be able to connect this way.




  When I say it hangs, I mean that nothing goes through on any database. The whole DB server just stops working. The OS and server is fine though. Its just the SQL Server instance.


Blocking

This sounds very much like you're experiencing a long blocking chain that never gets resolved - possibly due to long-running queries, but it could also be due to issues with replica synchronization.  Keep an eye out for LCK_* waits (for the blocking chains).  

Slow Synchronous Commits

You mentioned "always on" and if by this you mean Availability Groups, then look out for HADR_SYNC_COMMIT (for the replica synchronization issues).  If the secondary gets overloaded or overly busy, throughput could stall on the primary while waiting for acknowledgements from the secondary.

Poison Waits

It's also possible you're experiencing "poison waits."  Look carefully for those waits in the wait info you start logging (specifically RESOURCE_SEMAPHORE* and THREADPOOL).  This seems less likely to me though, as it probably shouldn't be going on for long periods of time.




  Btw, I am not a DBA but I dont quite get why SQL Server doesnt automatically failover since nothing works on master


This means the server is still responding to "are you up?" checks from the cluster manager, likely because the problem is blocking-related, which is "expected behavior" from SQL Server.  Things are running, they're just not running well.

Additionally, depending on your setup, you might never get automatic failover (if, for example, you have an availability group configured with only an async replica as the secondary).