How Many SQL Server ERRORLOG Files Do I Keep?

When working with SQL Servers there are lots of settings that people like to tweak to make their SQL Server run smoother.  One setting that I really like tweaking is the number of ERRORLOG files that are kept.  By default SQL Server keeps the last 6 ERRORLOG files around deleting the last one each time that the instance is restarted (or sp_cycle_errorlog is called).  But if I need to dig back a really long time 6 files may not be enough, especially if you cycle the ERRORLOG every day or week.

Because of this I will typically change the number of ERRORLOG files which are kept on the server to 20, maybe more (if the log will be cycled ever day).

You can change this setting from within Management Studio by right clicking on the “SQL Server Logs” folder in the object explorer and clicking “Configure”.  Just change the setting and click OK.  The system accepts any value from 6 to 99.  You can also script this using xp_instance_regwrite system stored procedure.

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'NumErrorLogs', REG_DWORD, 20
GO

Hopefully this comes in handy.
Denny

Share

Share on facebook
Share on twitter
Share on linkedin

One Response

Leave a Reply

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

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?