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
Hopefully this comes in handy.
Often forgotten, indeed.
Here are two Powershell alternatives:
– this one uses SQLSMO to set the number of errorlog files to keep:
– this one uses SQLServer: drive (SQL2012)