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.
