Capture SQL Server Connectivity Errors in the ERRORLOG

By: Sergey Gigoyan | Updated: 2021-02-18 | Comments (1) | Related: More > Database Administration

Problem

Suppose SQL Server users periodically report they lose connections to the database server. They also note that the connection interruptions are random and can happen several times per day with various intensity. In order to monitor these connectivity issues, you need to record these errors somehow. In this article we look at how to capture these errors in the SQL Server error log for further analysis.

Solution

As mentioned above, we are going to illustrate how to record information about connectivity issues into the SQL Server ERRORLOG file. To do this, we are going to demonstrate a simple connectivity issue and show how to capture the corresponding error message.

First, let’s understand what the ERRORLOG file is used for. While its name can be misleading, the ERRORLOG file contains not only error messages but also various information about database and server related processes. Thus, this file can be very helpful for database process monitoring.

When SQL Server is started or restarted, a new ERRORLOG file is created and the previous log files are renamed with an archive number. It is also possible to use the sp_cycle_errorlog procedure to cycle the log file without restarting the instance.

The default location of the ERRORLOG files is \Program Files\Microsoft SQL Server\MSSQL.<num>\MSSQL\LOG\ as shown below. We can also see the current file ERRORLOG and archive ERRORLOG files (the ones with a number). We can open the file via a text editor and see the content as shown below.

In SQL Server Management Studio we can view the error log as follows: Management > SQL Server Logs. Here we can see the Current log (ERRORLOG) and the backup files Archive #1 (ERRORLOG.1) to Archive #6 (ERRORLOG.6). We can view a log file by right-clicking on it and choosing View SQL Server Log.

Below we can see the contents.

Simulate Lost Connection

Now, let’s simulate a connectivity issue and see what will be registered in the ERRORLOG file. On the client’s computer (assumed the SQL Server instance is on the remote server and the client is connecting from the different computer), we open SQL Server Management Studio and a Query Window to run the code below.

SELECT @@SPID AS ProcessID
 
DECLARE @i INT=1
 
WHILE @i<=10
BEGIN
 
   SELECT @i
 
   SET @i=@i+1
 
   WAITFOR DELAY '00:00:02' 
END

While the query is still running, we disable the network connection on the client and we get the following error message.

We can enable the connection again and check out the ERRORLOG file to see if there any records about this dropped connection. As we can see, there is no information about this failure. This is because network disconnection errors are not recorded into the log file by default.

Enable Trace Flag 4029

In order to have the connectivity issues recorded in the ERRORLOG file we need to enable Trace Flag 4029. This flag is used to record network disconnect errors into the ERRORLOG file and it is disabled by default. It is important to mention that this flag is undocumented and there is no information about this flag in Microsoft’s official website.

The following code enables this trace flag and shows the current status of the flag:

USE master
GO

DBCC TRACEON(4029,-1)
GO
 
DBCC TRACESTATUS
GO

After running the above code, we can see the flag is globally enabled.

Well, to see the effect, we run the sample code above and again interrupt the connection.

SELECT @@SPID AS ProcessID
 
DECLARE @i INT=1
 
WHILE @i<=10
BEGIN
 
   SELECT @i
 
   SET @i=@i+1
 
   WAITFOR DELAY '00:00:02' 
END

The same network error will be generated.

However, when we open the ERRORLOG file this time, we can find the network error as shown below.

Disable Trace Flag 4029

If we do not want to capture these errors, we can turn off the trace flag as follows.

USE master
GO

DBCC TRACEOFF(4029,-1)
GO

Conclusion

To sum up, while connectivity errors are not recorded in the SQL Server ERRORLOG file by default, it is possible to change this behavior by enabling trace flag 4029. After doing that, we will be able to find connection related errors captured in the ERRORLOG file for father analysis.

Next Steps

Please use the links below to find additional topic related information:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-02-18

Friday, January 20, 2023 - 6:20:10 AM - Jay	Back To Top (90836)
So where are the steps to setup this logging then?