SQL Server Blog

Understanding and Managing SQL Server Error Log

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.

SQL Server offers a robust logging mechanism known as the SQL Server Error Log. This log plays a crucial role in troubleshooting, auditing, and ensuring the smooth operation of your SQL Server instances. You can explore the SQL server error log through SQL Server Management Studio in the management folder. There are various tools in the GUI to make navigation easier, as well as stored procedures to enable faster parsing of the data stored.

Purpose of the Error Log

The SQL Server Error Log is a file that records both system and user-defined events. It provides valuable information about the server’s activities and errors. For instance, it logs events such as client connections, SQL statements, server starts and stops, and error messages from server components. This information can be instrumental in detecting potential problem areas, auditing logins, and understanding the status of database recovery.

Number of Files Retained

By default, SQL Server retains six archived error log files. Each time the server starts, or the logs are cycled, a new error log file is created, and the existing files are renamed – the current log becomes errorlog.1, errorlog.1 becomes errorlog.2, and so on. The oldest log file is deleted if the maximum number of error log files exists. You can use the below script to determine how many log files your server is retaining:

EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'NumErrorLogs'

Cycling the Error Log

Cycling the error log is a process that creates a new log file and renames the existing ones. This can be done manually or automatically using the system stored procedure sp_cycle_errorlog. Cycling the error log helps keep the logs at a manageable size when reviewing the errors and prevents them from consuming too much disk space. We typically run both of the below scripts once per week, combined with a retention of 52 we have close to a year of retention. Close because the error log is cycled at every server restart as well.

Exec sys.sp_cycle_errorlog
Exec msdb.dbo.sp_cycle_agent_errorlog

Changing the Default Number of Error Log Files

While the default number of error log files retained is six, this can be increased up to 99. To change this, you can use SQL Server Management Studio (SSMS) and navigate to the Configure SQL Server Error Logs dialog box. Here, you can specify a different maximum number of error log files.

For instance, to increase the number of error log files to 52, check the box labeled “Limit the number of error log files before they are recycled” and enter 52 in the “Maximum number of error log files” field. Remember to restart the SQL Server service to apply the changes. To make this change via T-SQL, you can use the below script:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 52

In conclusion, the SQL Server Error Log is a powerful tool for maintaining and troubleshooting your SQL Server instances. Regularly cycling the error log and adjusting the number of log files retained can help manage disk space and improve server performance. As always, remember to monitor your logs regularly to catch potential issues early and keep your databases running smoothly.

David Seis
Article by David Seis
David Seis is a Support Technician that joined Straight Path in the summer of 2021. His goal is to work in an area that he believes helps others, and to do it to the best of his ability. He loves to help, solve problems, and make things easy for others to understand. Because of this he is lovingly known as a “Good helper” among friends and family. Outside of work, he has a growing passion for cybersecurity, specifically helping protect those who are most vulnerable to fraud, scams, and phishing. He looks forward to learning more about each of his clients, helping minimize issues, and ensure that any problems are solved quickly.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This