Since last wednesday (so a we're dealing with this for a week now) we're getting periodic connection problems on our production database cluster. (An alwayson cluster running on windows 2012, three machines, two sync and one async).
When it starting we noticed the following two messages in the SQL Event log that have been repeating ever since when the problem arise;
- Error: 18456, Severity: 14, State: 46.
- Login failed for user 'xxx' Reason: Failed to open the database 'xxx' configured in the login object while revalidating the login on the connection. [ CLIENT: xxx.xxx.xxx.xxx]
We've been trying just about everything and are reaching desperation currently. The things we've tried;
- Restart our firewalls
- Restart our switches
- Failover and reboot the nodes (one by one)
- Turn off the async node and see if anything changes
- Turn off all but the primary node to see if anything changes
- Replace the switch
- Remove some of the network cables and run on one NCI
- Remove the teaming from a server and failover to this server
Non of this seems to do anything.
The errors connecting code receives vary from a Timeout when beginning a transaction to a Timeout executing a query. (A query that does not show up in SQL Profiler when looking for queries running longer than 5000ms).
The strangest error however to me is the following;
System.Data.SqlClient.SqlException: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=0; handshake=14991;
Now I in no way consider myself a real DBA, hell I manage the software development department and am actually looking to hire one currently... However in my experience and with my limited knowledge I feel it is very odd to not see any of these queries that are timing out in my profiler...
As a matter of fact, I see no blocking queries or queries that raise my suspicion in any way...
Another thing to add here is that it seems to come in waves... Sometimes everything is fine for 15-25 minutes and then it's bad for ~2-5 minutes. This happens the whole day as well, so not during peak hours or when our normal processes run. (Basically I'm seeing errors from our API's trying to reach the database, very few processes run @ night and non of them this periodically).
It's the whole network experiencing this as well, meaning I can rule out code since we didn't publish any changes nor do we connect to the database on the same fashion. (Some through NHibernate, some ADO.NET, some old ADO)
The biggest changes happened on the network side but our sys admin seems adamant it can't be since non of the changes should affect this and they can't see anything on the switches that are suspicious.
Does anyone here have any idea what we could try / should look at next?