Quick Scan Report – Error Log Retention

What this check looks for

The NumErrorLogs registry value under Software\Microsoft\MSSQLServer\MSSQLServer, read with xp_instance_regread. The default is 6, and the recommendation is 20 or 30.

The check needs sysadmin to read the registry, and the read is wrapped so that Linux, Amazon RDS or a locked down registry produce no finding rather than an error.

This page also covers the related finding raised against issue 65’s second URL, MaintenancePlansAndJobs, which points at the same remedy.

Why it matters

The error log is where SQL Server records the things nothing else records, and the retention setting decides how far back you can look.

What lives only there:

• DBCC CHECKDB results, including corruption findings.
• Errors 823, 824 and 825, the I/O and corruption errors.
• Failed logins, when auditing is on.
• The reason for a restart, a failover or a crash.
• Long I/O warnings, which are storage problems stated plainly.

The log rolls over on two triggers: every service restart, and every sp_cycle_errorlog. With six files kept, six restarts is all it takes to lose everything before them. On a server patched monthly and restarted for each patch, plus the occasional failover, six files can be three weeks or three days depending on what happened.

The failure this produces is specific and painful. Somebody asks what happened last Tuesday. The instance was restarted on Wednesday and again on Friday. The log that would answer is gone, and no amount of investigation brings it back.

There is a second problem this setting interacts with. Instances that do not cycle the log at all end up with one enormous file covering months, which is unreadable and slow to open, and has its own checks. The right arrangement is both: cycle weekly, and keep enough files that weekly cycling still gives you months of history. Cycling without raising the retention makes things worse, because it rolls the useful history out faster.

How to confirm it yourself

DECLARE @NumErrorLogs INT;
EXEC master.dbo.xp_instance_regread
     N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'NumErrorLogs',
     @NumErrorLogs OUTPUT;
SELECT ISNULL(@NumErrorLogs, 6) AS [logs_retained];

A null means the value has never been set, so the default of 6 applies.

What you actually have right now, which is the more useful view:

EXEC sp_enumerrorlogs;

That lists each log file with its date and size. Read the oldest date. That is how far back your history goes, and it is usually a surprise.

Whether anything cycles it:

SELECT j.[name], j.[enabled], s.[name] AS [schedule]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
  LEFT JOIN msdb.dbo.sysjobschedules AS js WITH (NOLOCK) ON js.[job_id] = j.[job_id]
  LEFT JOIN msdb.dbo.sysschedules AS s WITH (NOLOCK) ON s.[schedule_id] = js.[schedule_id]
 WHERE j.[name] LIKE '%cycle%' OR j.[name] LIKE '%errorlog%';

How to fix it

The Quick Scan offers this directly. Right click the finding and choose to set retention to 10, 20 or 30, or the combined action that sets it to 20 and creates a weekly cycling job. That last one is the complete fix in a single click.

Behind them:

EXEC xp_instance_regwrite
     N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'NumErrorLogs',
     REG_DWORD,
     30;

This takes effect immediately, with no restart.

Then cycle the log on a schedule, because retention without cycling gives you six enormous files rather than thirty readable ones:

EXEC msdb.dbo.sp_cycle_errorlog;

as a weekly Agent job. With 30 files kept and weekly cycling, you have roughly seven months of history in files small enough to open.

Choose the two numbers together:

Cycle the Agent log too, which is separate and has the same problem:

EXEC msdb.dbo.sp_cycle_agent_errorlog;

Size is not a concern. Error logs are text and a busy instance produces a few megabytes a week. Thirty of them is smaller than one backup.

How long it takes

About an hour, including creating the cycling job and checking the Agent log too.

Related reports

Related checks

Frequently asked questions

Does raising the retention need a restart? No. The registry value is read when the log is cycled, so the next cycle uses the new number.

How many should I keep? 30 with weekly cycling is a good default and gives about seven months. 20 is fine if you cycle weekly and only need a quarter.

Will 30 log files use much disk? No. Each is text, typically a few megabytes. On an instance where they are large, that volume is itself the finding and has its own check.

We keep logs in a monitoring system instead. Then history is covered, and it is still worth raising this so the local copy is usable during an incident when the monitoring system may be the thing you cannot reach.