Problem: About once a month, I get support ticket regarding SQL Server dropping an application’s or user’s connection. The problem is SQL Server does not just randomly drop a connection and continue to work normally. Some force outside the control of SQL Server breaks the connection. By default, SQL Server does not record when this event occurs.
In my history of working with SQL Server, only in extreme situations have I ever seen SQL Server drop its connections. The most common example is when SQL Server is in the process of shutting down.
Troubleshooting: Trace flag 4029 can help in trouble shooting this issue. Information about the date, time, and SPID, along with how SQL Server sees this event happening is recorded to its error log.
Please note that I have not found anything about this trace flag in MSDN or Technet, so this is probably an undocumented trace flag.
In Yusuf Anis’s article “SQL Server Trace Flags” on the SQL Server Central website
describes the trace flag 4029: “Logs extended errors to errorlog when network disconnect occurs, turned off by default. Will dump out the socket error code this can sometimes give you a clue as to the root cause.”
In many posts on this trace flag is paired with trace flag 3689. In my testing, this trace flag is only necessary with versions of SQL Server 2000 or earlier. This trace flag enabled the error to be written to the error log. With versions SQL Server 2005 & later, this trace is not required with 4029, but does no harm if enabled.
To enable trace flag, use the “DBCC TRACEON” command with the additional “-1” trace flag to sets this as a global trace flag for all connections. This trace flag will remain active until turned off or SQL Server is restarted.
DBCC TRACEON(4029,-1); GO
Verify that the trace flag’s status by running DBCC TRACESTATUS,
DBCC TRACESTATUS; GO
Also the trace flag can be set when SQL Server starts by adding the “-T4029” in the Startup Parameters of the instance properties in the SQL Server Configuration Manager.
When a lost or dropped connection event occurs:
In my example I killed the SQL Server Studio Manager process in the task manager while running DBCC CHECKDB.
In the SQL Server error log I am able to see the event:
2017-12-09 19:27:06.930 Server Error: 7885, Severity: 20, State: 1.
2017-12-09 19:27:06.930 Server Network error 0x2746 occurred while sending data to the client on process ID 57 batch ID 0. A common cause for this error is if the client disconnected without reading the entire response from the server. This connection will be terminated.
Using XP_READERRORLOG, I am able to see the error log in the results tab of a query window. With this information, I was able to see that the network error was cause by a client disconnect. This would shift my focus from SQL Server back to the client server.
