SQL Server Error Log Management

Updated: Sep 16, 2020

SQL Server maintains its own log, also called “SQL Server Error Log”. This log contains messages describing informational and error events, similar to messages that you can find in Windows logs. In fact, many of the messages found in the SQL Server Error Log can also be found in the Windows Application Log. The SQL Server Error Log is a great place to find information about what’s happening on your database server.


SQL Server uses 7 log files to store these messages. One file serves as the current log file, and every new message is written to that file. The other 6 files are archived files, and they contain previous messages. Each time SQL Server is restarted, it recycles the files. What does it mean? First, it means that a new log file is created and becomes the new current log file. Second, the oldest log file (“Archive #6”) is deleted. And third, all the other log files are pushed back one step. The previous current log file becomes “Archive #1”, the previous “Archive #1” log file becomes “Archive #2”, and so on.



The log files are stored in the log folder of the instance. This folder is located by default in “Program Files\Microsoft SQL Server\MSSQL{nn}.MyInstance\MSSQL\Log”. You can configure the location of the log folder during installation, and you can also change it using the “-e” startup parameter of the SQL Server service.


So far I described the default behavior of SQL Server. There are three problems with the default behavior of SQL Server. The first problem is that you have no control over the size of the log files. One file can be very large while another file can be very small. It would be easier to manage these files and work with them if they had a more controlled and predictable size. The second problem is that you have no control over the recycling process. One file can contain two months of messages, while another file can contain only a few hours of messages. It’s difficult to find a specific message from a specific point in time. The third problem with the default behavior of SQL Server is that there are only 7 log files, and if recycling happens too often, then you might not have enough history.


In order to solve the first two problems mentioned above, all we need to do is to control the recycling process. The sys.sp_cycle_errorlog system stored procedure will do the job. Each time you call this stored procedure, SQL Server performs the recycling process as mentioned above. So what you can do is create a job that runs on a fixed schedule (let’s say – daily), which calls this stored procedure. This way, each log file will contain messages for a single day. The result is a more predictable size for the log files, and it’s also easier to locate messages from a specific day.


In order to solve the third problem (not enough history), all we need to do is to increase the number of log files. You can determine how many archive log files to keep (in addition to the current log file). The default is 6, but you can change it to any number between 6 and 99. One way to do it is by right-clicking the “SQL Server Logs” folder in SSMS and choosing “Configure”. You will be presented with the following dialog:



Check the checkbox and specify the number of archive log files you would like to have. For example, I usually use 30 log files, one for each day, so at any point in time, I have a log history of 30 days.


Notice that you can also limit the size of each error log file. If you are going to recycle the error log on a daily basis, then limiting the size might not be necessary. Nevertheless, it is a good practice to limit the size as well, just to make sure that no single file becomes too large.


Instead of using the GUI, you can also use the following statements:


EXECUTE sys.xp_instance_regwrite

N'HKEY_LOCAL_MACHINE' ,

N'Software\Microsoft\MSSQLServer\MSSQLServer' ,

N'NumErrorLogs' ,

REG_DWORD ,

30;

GO


EXECUTE sys.xp_instance_regwrite