I am losing my patience with this problem.
I did see a couple of other questions here that are similar, but I've already looked at the fix actions that resolved their issues and they did not apply to this scenario.
I'm running SQL Server 2008 R2 with all updates. When this particular issue occurs my servers lose all connection ability to the server and it has to be restarted.
It is very intermittent. Every now and again this SQL server instance will report the error: Login failed for user 'XX'. Reason: Failed to open the database configured in the login object while revalidating the login on the connection. Error: 18456, Severity: 14, State: 46.
I know this is a good login, it works all of the other times, and no one changes the credentials.
The above error is immediately followed by this error:
The client was unable to reuse a session with SPID 150, 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.
Error: 18456, Severity: 14, State: 46.
When this occurs a few minutes later this is reported also:
Message All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on ASYNC_NETWORK_IO. Process Utilization 0%.
The above message just keeps repeating until I restart the server.
From researching this issue I've increased the maximum worker threads (currently at 1100). The server has 72G memory and 4 vCPUs, running Windows Server 2008 R2.
I don't encounter this issue at all on my other database servers that run the same environment. Just this one. Any help would be greatly appreciated, I'm running out of options.