How Many SQL Server ERRORLOG Files Do I Keep?

Published On: 2013-04-03By:

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


Contact the Author | Contact DCAC

One response to “How Many SQL Server ERRORLOG Files Do I Keep?”

  1. itdidntrecognizeme says:

    Often forgotten, indeed.

    Here are two Powershell alternatives:
    – this one uses SQLSMO to set the number of errorlog files to keep:
    http://powershell.com/cs/media/p/20796.aspx

    – this one uses SQLServer: drive (SQL2012)
    http://powershell.com/cs/media/p/20804.aspx

    Great post.
    Johan

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link