Error Log Issues

What this check looks for

Entries in the SQL Server error log whose text contains error or failed, with the top 20 reported by date.

A good deal of filtering happens first, because the error log contains a lot of text with the word “error” in it that is not an error. Excluded are logon entries, and any line matching:

  • found 0 errors, without errors, no errors (CHECKDB reporting success)
  • The error log has been reinitialized (a log cycle, not a problem)
  • Registry startup parameters and -e C: (startup banner lines naming the log path)
  • Logging SQL Server messages in
  • xpstar.dll
  • Machine supports memory error recovery

What is left is entries worth a human reading.

Why it matters

The error log is the only place several classes of problem are recorded, and it is write-only on most instances. Nobody reads it. Everyone means to.

The things that appear there and nowhere else:

  • Corruption. CHECKDB writes its findings here. Errors 823, 824 and 825 arrive here first, and 825 in particular is the one that matters most: it means a read succeeded after retrying, which is the warning that comes before real corruption, and it is not raised to anything that would alert you.
  • Login failures, if auditing is on.
  • Backup failures, including backups taken by third party tools that do not report to Agent.
  • Memory and I/O warnings, such as a long I/O request taking more than 15 seconds, which is a storage problem stated plainly.
  • Service and startup problems, including files that could not be opened and databases that did not recover.

This check is a prompt, not a diagnosis. Twenty matching lines could be twenty copies of one harmless message, or a single line reporting corruption. What it does is put the log in front of you, which for most instances is the first time in a long time.

How to confirm it yourself

Read the current log:

EXEC sp_readerrorlog 0, 1;

Filter it, which is what makes it usable:

EXEC sp_readerrorlog 0, 1, N'error';
EXEC sp_readerrorlog 0, 1, N'failed';

The parameters are: log number, where 0 is current and 1 is the previous, then 1 for the SQL Server log rather than the Agent log, then up to two search strings.

Read the older logs too. A restart cycles the log, so the entries explaining a restart are in the previous one:

EXEC sp_enumerrorlogs;   -- how many there are, and how big
EXEC sp_readerrorlog 1, 1, N'error';

The ones to look for specifically, in order of how much they matter:

EXEC sp_readerrorlog 0, 1, N'823';
EXEC sp_readerrorlog 0, 1, N'824';
EXEC sp_readerrorlog 0, 1, N'825';
EXEC sp_readerrorlog 0, 1, N'CHECKDB';
EXEC sp_readerrorlog 0, 1, N'I/O requests taking longer than 15 seconds';

How to fix it

There is no single fix, because this check reports whatever is in there. The work is triage.

  1. Read what was reported. The scan report shows the date and text of each entry, and the Go To Error Log Report action in the finding’s menu opens the full log.
  2. Sort real errors from noise. A repeated message from an application connecting badly is noise you should still fix, at the source. A single CHECKDB message is not noise.
  3. Act on the serious ones first. Corruption messages have their own check and their own urgency. I/O warnings are a storage conversation. Login failures are a security one.
  4. Reduce the noise so the log stays readable. This is the part that makes the next review possible:
    • Cycle the log regularly. A weekly Agent job running sp_cycle_errorlog keeps each file a readable size. The finding’s menu offers this directly.
    • Keep more log files. The default of 6 is too few once you cycle weekly. 26 gives you six months.
    • Turn off successful backup logging if backup messages dominate, which they usually do. Trace flag 3226 suppresses them, and they are in msdb backup history anyway.
-- keep 26 error log files instead of 6
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 26;
  1. Set up alerts so you are not relying on a scan. SQL Server Agent alerts on severity 19 through 25, and on errors 823, 824 and 825 specifically, mean the serious entries reach a person the day they happen.

How long it takes

About an hour to read the log and triage what is in it. Acting on individual findings varies entirely.

Report Why you would go there
Error Log The full log, searchable, rather than twenty lines.
Suspect Pages Whether any corruption message has already been recorded.
Last DBCC CheckDB Known Good by Database Whether the CHECKDB messages are recent.
Alerts and Operators Whether anything would tell you about the next one.
Failed Jobs Job failures that also appear in the log.
I/O by Drive The storage behind an I/O warning.
Check
DBCC CHECKDB Corruption Errors Found The most serious thing this check can surface.
Serious errors, severity 19 to 25 The high severity subset on its own terms.
SQL Server errors 823, 824 and 825 The I/O error subset on its own terms.
Large error log files A log too large to read, which is why nobody does.
Error log not recycled The cause of that.
Slow error log access A log so large that reading it is itself a problem.

Frequently asked questions

Most of these are backup messages. Then trace flag 3226 is worth setting. Successful backup messages are recorded in msdb backup history regardless, so suppressing them in the log loses nothing and makes the log readable.

Why only 20 entries? Because it is a scan, not a log reader. The Error Log report shows everything, and the queries above search the whole log.

The same error appears dozens of times. That is useful information in itself. A repeated error is usually an application retrying, and the fix is at the source rather than in the log.

Does this check read old log files? It reads the error log as collected, which includes the historic files. That matters because a restart cycles the log, and the entries explaining a restart are in the previous file.