SQL Server with Mr. Denny

Apr 3 2013   2:00PM GMT

How Many SQL Server ERRORLOG Files Do I Keep?

Denny Cherry Denny Cherry Profile: Denny Cherry

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'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 20
GO

Hopefully this comes in handy.
Denny

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: