Information
SQL Server error log files must be protected from loss. The log files must be backed up before they are overwritten. Retaining more error logs helps prevent loss from frequent recycling before backups can occur.
Rationale:
The SQL Server error log contains important information about major server events and login attempt information as well.
Solution
Adjust the number of logs to prevent data loss. The default value of 6 may be insufficient for a production environment. Perform either the GUI or T-SQL method shown:
GUI Method
Open SQL Server Management Studio.
Open Object Explorer and connect to the target instance.
Navigate to the Management tab in Object Explorer and expand. Right click on the SQL Server Logs file and select Configure
Check the Limit the number of error log files before they are recycled
Set the Maximum number of error log files to greater than or equal to 12
T-SQL Method
Run the following T-SQL to change the number of error log files, replace <NumberAbove12> with your desired number of error log files:
EXEC master.sys.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'NumErrorLogs',
REG_DWORD,
<NumberAbove12>;
Impact:
Once the max number of error logs is reached, the oldest error log file is deleted each time SQL Server restarts or sp_cycle_errorlog is executed.
Default Value:
6 SQL Server error log files in addition to the current error log file are retained by default.
References:
http://msdn.microsoft.com/en-us/library/ms177285(v=sql.110).aspx