By default, 6 old SQL Server error log files are available on our SQL server instance and the older ones are recycled. However, sometimes, we may require the error log files older than the default. Its possible to increase the number of old log available by using both SSMS and t-sql query.
To configure the setting using SSMS, we need to follow the steps mentioned below:
1. Connect to the SQL Server instance using SSMS and browse to Node Management –> SQL Server Logs.
2. Right-click on SQL Server Logs and select Configure.
3. Select the checkbox next to Limit the number of error log files before they are recycled and select the number of error log files we required. The configurable values for SQL Server error log files are between 6-99.
Note: The increased number of SQL Server Error logs will consume space on our hard disk, so we need to make sure there is enough disk space available on the drive for other required operation.
The below query will increase the number of SQL Server error log files to 10 from the default value of 6.
USE [master]; GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10; GO