Increase SQL Server Error Log Files

Recently I learned that you can and probably should increase the number of SQL Server Error logs that your instance of SQL Server maintains.  This is real simple to change.  You can make the change via the GUI with a few simple steps or accomplish it with TSQL.

To make this change with SSMS, open SSMS, connect to your instance, expand Management, right click on SQL Server Logs and chose Configure.  You can then check the box “Limit the number of error log files before they are recycled” and increase the number from 6 to any number up to 99. 

Using TSQL you can execute the following statement to increase to 99 files, simply change 99 to how ever many files you would like to retain.

USE [master];
GO

EXEC xp_instance_regwriteN’HKEY_LOCAL_MACHINE’,N’SoftwareMicrosoftMSSQLServerMSSQLServer’,N’NumErrorLogs’, REG_DWORD, 99;

GO

Share
This entry was posted in SQL Tuning, SQLServerPedia Syndication, Tibits. Bookmark the permalink.

5 Responses to Increase SQL Server Error Log Files

  1. AndySugs says:

    Why should we increase the number? What purpose does it serve Tim?

    • Tim Radney says:

      Increasing the number of error logs allow you to see much further into the past for issues. On some of my larger servers where audit successful and failed logins the log fills up pretty darn quick. With only 6 files I can’t see but a couple of days.

  2. AndySugs says:

    Good stuff. Thanks Tim

  3. AHPerez says:

    Simple and effective. Thanks for the post.

  4. Leonard says:

    at the same time, you should also set up a job to recycle the error logs on a scheduled basis. I changed the number of error logs to 26, then scheduled a job to recycle on a weekly basis. That gives me 6 months of error logs as an archive(unless you do a reboot, which cycles the log as well.

    It also makes it easier to open the log, if you have to browse it.
    MSDB..sp_cycle_errorlog

    Leonard

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>