I'm looking at a database where every so often, connections to it will fail. The event viewer and SQL Logs have the following error in them:
Server local connection provider has stopped listening on [ \.\pipe\SQLLocal\MSSQLSERVER ] due to a failure. Error: 0xe8, state: 4. The server will automatically attempt to re-establish listening.
I'm trying to determine the cause of this, as it's taking down the production database once or twice a day. Most of the time, it will reconnect within 30 seconds, but sometimes it can take 30+ minutes, and at that point, the customer restarts the SQL server, which gets it working again.
Here is what I've investigated so far:
- Configuration Manager had all 3 protocols (shared memory, TCP/IP, named pipes) enabled for all options. We have since disabled named pipes.
- sys.dm_exec_connections shows that 34 of the 45 connections to the DB right now are shared memory, so I don't think disabling named pipes did anything.
- Shared memory is just named pipes on a local machine.
- There are no additional events at the time of these errors in the event viewer. I've looked throughout Applications and Services logs as well as the Windows Logs, and nothing else is logged at the same time.
Connections work if we specify the hostname with tcp: in front of it. This seems to be an issue entirely with shared memory.
I've enabled trace flags 3689,4029 according to this post
- Once the traceflags were enabled, I've seen additional entries in the event viewer like the following: Network error code 0xe8 occurred while establishing a connection; the connection has been closed. This may have been caused by client or server login timeout expiration. Time spent during login: total 471 ms, enqueued 12 ms, network writes 0 ms, network reads 206 ms, establishing SSL 252 ms, network reads during SSL 2 ms, network writes during SSL 0 ms, secure calls during SSL 0 ms, enqueued during SSL 249 ms, negotiating SSPI 0 ms, network reads during SSPI 0 ms, network writes during SSPI 0 ms, secure calls during SSPI 0 ms, enqueued during SSPI 0 ms, validating login 0 ms, including user-defined login processing 0 ms. [CLIENT: local machine]
- The above error happened about 15 seconds before the connection stopped listening.
- These errors are a lot more common when we have Sophos Endpoint protection enabled, but they still happened once when it was disabled.
SQL server @@version string:
Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64) Sep 23 2016 16:56:29 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
It's only SP2, so we may try upgrading the DB in case this got fixed.
Does anyone have ideas on where to go from here? I'm considering disabling Shared Memory, as that seems to be the culprit, but based on what I've read, I'm going to need to do a lot more research on that.