I recently had done a SQL patch on a two node SQL availability group of SQL Server 2012. Everything went well as far as I can tell. Now two days after the patch unexpected failover happened. Then today as well unexpected failover happened.
For a temporary period, the primary of the availability group goes in to resolving state and forces it into failover to resume data movement as normal.
Now, I am not sure if this is anything related to the patch. Because the error logs show other issues. Here are the logs from the two times this happened.
Date 6/11/2018 4:40:06 AM Log SQL Server (Current - 6/11/2018 2:25:00 PM) Source Logon Message Error: 983, Severity: 14, State: 1.
Date 6/11/2018 4:40:06 AM Log SQL Server (Current - 6/11/2018 2:25:00 PM) Source Logon Message Unable to access database 'accounting' because its replica role is RESOLVING which does not allow connections. Try the operation again later. Date 6/11/2018 4:40:06 AM Log SQL Server (Current - 6/11/2018 2:25:00 PM)
And today Date 6/14/2018 3:13:47 PM Log SQL Server (Current - 6/14/2018 3:14:00 PM) Source spid6s Message SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\EveriT31_Park.mdf] in database [EveriT31_Parx] (21). The OS file handle is 0x0000000000000B00. The offset of the latest long I/O is: 0x00000002750000
Date 6/14/2018 3:14:04 PM Log SQL Server (Current - 6/14/2018 3:14:00 PM) Source spid127 Message Error: 18056, Severity: 20, State: 46.
Date 6/14/2018 3:14:04 PM Log SQL Server (Current - 6/14/2018 3:14:00 PM) Source spid127 Message The client was unable to reuse a session with SPID 127, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. Date 6/14/2018 3:14:05 PM Log SQL Server (Current - 6/14/2018 3:14:00 PM)
The AG is set for automatic failover but the alwayson group is not really changing roles. After these episodes, the primary remains primary and the secondary remains secondars.
Every log I look at the first error I get is either the I/O issue or connection pool error (Error: 18056, Severity: 20, State: 46.). I hesitate to say the problem is really that. I don't think it is related with the SQL patch I just did. I don't see anything that is related to it and I want to rule that one out.
Can anyone suggest what might be causing these? I looked at some articles, but none gave me a clear answer.