14

I know I can cycle the current error log easily by running sp_cycle_errorlog, but I'm wondering if SQL Server will ever delete the old/archived error log files at all. Can't seem to find an answer to this anywhere...

Improve this question
0

4 Answers 4

Reset to default
23

In SQL Server Management Studio, in Object Explorer > Management, there is a setting if you right click on SQL Server Logs and select Configure. You can specify how many log files you want to keep. Once it reaches that number it will start removing the old ones.

enter image description here

Improve this answer
8

Restart SQL Server 7 times. You will see you still have only 7 ERRORLOG* files (depending on version). This is the current log file and the 6 most recent log files.

Note: You may need to change 7 to something else, in the event you changed the number of error logs SQL Server keeps. The max, I believe, is 99. But no matter what your current setting is, it will eventually recycle and purge old files.

I tested this and, if you uncheck the box that says:

☐ Limit the number of error log files before they are recycled

...which implies (at least to me) keep all log files, it will still keep only the current error log file plus 6 backups. If you've changed the registry key that @RLF pointed out, the registry key setting overrides (as if you hadn't unchecked the box), but you're still going to be limited to some number of backup error log files between 6 and 99.

So the short answer is: yes, in all cases, SQL Server will eventually get around to deleting old error log files.

Improve this answer
7

SQL Server recycles error logs automatically, as long as you configure it correctly.

See http://msdn.microsoft.com/en-us/library/ms177285.aspx . I've copied the salient points below:

In Object Explorer, expand the instance of SQL Server, expand Management, right-click SQL Server Logs, and then click Configure.

In the Configure SQL Server Error Logs dialog box, choose from the following options.

  • Limit the number of the error log files before they are recycled

    • Check to limit the number of error logs created before they are recycled. A new error log is created each time an instance of SQL Server is started. SQL Server retains backups of the previous six logs, unless you check this option, and specify a different maximum number of error log files below.

  • Maximum number of error log files

    • Specify the maximum number of error log files created before they are recycled. The default is 6, which is the number of previous backup logs SQL Server retains before recycling them.
Improve this answer
2

Yes, there is a configuration for how many errorlogs to maintain.

It can be found in the registry key at:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs

You may need to add this key.

Improve this answer
1
  • 1
    Note that you need to replace the first instance of MSSQLServer with your instance ID if it's a named instance (e.g. MSSQL12.SQL2014 for my 2014 named instance named SQL2014).
    – Aaron Bertrand
    Commented Oct 29, 2014 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.