Increase SQL Server Error Log Files
Recently I learned that you can and probably should increase the number of SQL Server Error logs that your instance of SQL Server maintains. This is real simple to change. You can make the change via the GUI with a few simple steps or accomplish it with TSQL.
To make this change with SSMS, open SSMS, connect to your instance, expand Management, right click on SQL Server Logs and chose Configure. You can then check the box “Limit the number of error log files before they are recycled” and increase the number from 6 to any number up to 99.
Using TSQL you can execute the following statement to increase to 99 files, simply change 99 to how ever many files you would like to retain.
USE [master];
GO
EXEC xp_instance_regwriteN’HKEY_LOCAL_MACHINE’,N’SoftwareMicrosoftMSSQLServerMSSQLServer’,N’NumErrorLogs’, REG_DWORD, 99;
GO
5 Comments
Why should we increase the number? What purpose does it serve Tim?
Increasing the number of error logs allow you to see much further into the past for issues. On some of my larger servers where audit successful and failed logins the log fills up pretty darn quick. With only 6 files I can’t see but a couple of days.
Good stuff. Thanks Tim
Simple and effective. Thanks for the post.
at the same time, you should also set up a job to recycle the error logs on a scheduled basis. I changed the number of error logs to 26, then scheduled a job to recycle on a weekly basis. That gives me 6 months of error logs as an archive(unless you do a reboot, which cycles the log as well.
It also makes it easier to open the log, if you have to browse it.
MSDB..sp_cycle_errorlog
Leonard