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.