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:
