{"id":1514,"date":"2017-12-10T01:59:42","date_gmt":"2017-12-10T01:59:42","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=1514"},"modified":"2017-12-10T13:52:59","modified_gmt":"2017-12-10T13:52:59","slug":"trace-flag-for-troubleshooting-lost-or-dropped-connections-in-sql-server","status":"publish","type":"post","link":"http:\/\/www.theSQLReport.com\/?p=1514","title":{"rendered":"Trace Flag for Troubleshooting Lost or Dropped Connections in SQL Server"},"content":{"rendered":"

Problem:<\/strong> About once a month, I get support ticket regarding SQL Server dropping an application’s or user’s connection.\u00a0 The problem is SQL Server does not just randomly drop a connection and continue to work normally.\u00a0 Some force outside the control of SQL Server breaks the connection.\u00a0 By default, SQL Server does not record when this event occurs.<\/p>\n

In my history of working with SQL Server, only in extreme situations have I ever seen SQL Server drop its connections.\u00a0 The most common example is when SQL Server is in the process of shutting down.<\/p>\n

Troubleshooting:<\/strong> Trace flag\u00a04029 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.<\/p>\n

Please note that I have not found anything about this trace flag in MSDN or Technet, so this is probably an undocumented trace flag.<\/p>\n

In Yusuf Anis’s article “SQL Server Trace Flags<\/a>” on the SQL Server Central website
\ndescribes the trace flag 4029:\u00a0 “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.”<\/p>\n

In many posts on this trace flag is paired with trace flag 3689.\u00a0\u00a0 In my testing, this trace flag is only necessary with versions of SQL Server 2000 or earlier.\u00a0 This trace flag enabled the error to be written to the error log.\u00a0 With versions SQL Server 2005 & later, this trace is not required with 4029, but does no harm if enabled.<\/p>\n

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.\u00a0 This trace flag will remain active until turned off or SQL Server is restarted.<\/p>\n

DBCC TRACEON(4029,-1);\r\nGO<\/pre>\n
\"DBCC<\/a><\/p>\n
Verify that the trace flag’s status by running DBCC TRACESTATUS,<\/p>\n
DBCC TRACESTATUS;\r\nGO<\/pre>\n
\"Verify<\/a><\/p>\n
Also the trace flag can be set when SQL Server starts\u00a0by adding the “-T4029” in the Startup Parameters of the instance properties in the SQL Server Configuration Manager.<\/p>\n
\"Startup<\/a><\/p>\n
When a lost or dropped connection event occurs:<\/strong><\/p>\n
In my example I killed the SQL Server Studio Manager process in the task manager while running DBCC CHECKDB.<\/p>\n
In the SQL Server error log I am able to see the event:<\/p>\n
2017-12-09 19:27:06.930 Server\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Error: 7885, Severity: 20, State: 1. <\/em><\/p>\n
2017-12-09 19:27:06.930 Server\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 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.<\/em><\/p>\n
Using XP_READERRORLOG, I am able to see the error log in the results tab of a query window.\u00a0 With this information, I was able to see that the network error was cause by a client disconnect.\u00a0 This would shift my focus from SQL Server back to the client server.<\/p>\n
\"Error:<\/a><\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Problem: About once a month, I get support ticket regarding SQL Server dropping an application’s or user’s connection.\u00a0 The problem is SQL Server does not just randomly drop a connection … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":1521,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[117,118,119,116],"class_list":["post-1514","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-117","tag-dbcc-traceon","tag-network-error","tag-trace-flag"],"_links":{"self":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1514"}],"collection":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1514"}],"version-history":[{"count":10,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1514\/revisions"}],"predecessor-version":[{"id":1528,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1514\/revisions\/1528"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/1521"}],"wp:attachment":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1514"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1514"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1514"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}