Error Log

Overview

The Error Log report shows what SQL Server has been writing down about itself, and – more usefully – which of it is worth reading.

An error log is not a list. It is a timestamped, categorised, severity-tagged event stream, and every one of those properties is sitting in the message text. This report reads them:

  • Any retained log, not just the current one. The picker lists every archive the instance still holds, with its date range and size, and the arrows walk backwards through them one at a time. If Saturday’s outage is on the other side of a Sunday-night cycle, it is one click away.
  • The SQL Server Agent log too. Same procedure, one argument different.
  • A class per line. Fatal, Error, Warning, Security, Routine, Info – derived from the severity and error number where the line carries them, and from the message where severity would be misleading.
  • Grouping. A busy instance writes the same twenty messages several thousand times. Grouped on the normalised message, those thousands become twenty rows.
  • The lines around the one you opened. A SQL Server error is almost never one line.
The Error Log report
The whole report. Tiles across the top, the hourly histogram under them, a lane per category below that.
The error log lanes
One lane per category, one tick per entry. The histogram above shows the hour each tick belongs to, and clicking a bar filters the page to that hour.

Where to find it

An instance level report. Right-click the server → Instance Level Reports → Error Log.

Reading the page

The tiles

Tile What it counts
Entries Every line read, before any filter, with the shape of the log under the number.
Errors Severity 17 and above, plus anything that produced a stack dump.
Security Logins, permissions and audit – with the number of distinct accounts involved.
Log covers The span between the oldest and newest entry actually returned.
Routine Log backups, clean CHECKDBs, the startup banner – counted whether or not they are on screen.

The chart

Four views, on the segmented control in the toolbar.

Time – one lane per category, one tick per entry, every lane against a single axis. Density inside a lane is that subsystem’s rhythm: a backup lane that is a row of evenly spaced clumps is a schedule, a storage lane that is a solid rug from Tuesday on is a failing disk. Because the lanes share one axis, things lining up vertically happened together – three lanes lighting up in the same column at 02:14 is one incident, not three.

The histogram above the lanes is the whole log’s shape. Click a bar to narrow the grid to that hour; click it again to clear it. Click a lane to narrow the grid to that category. Double-click a tick to open that entry in the detail pane.

Messages – one bar per distinct message with the variable parts removed, biggest first. This is the view that makes a log readable. Click a bar to show only that message in the grid.

Sources – one bar per errproc, split by class. Answers “is this the engine, a subsystem, or one session”.

Clock – a 24-hour dial, two rings deep. The outer wedge is everything written in that hour; the inner overlay is only the part of it that was serious. A tall wedge that is all outer ring is a schedule. A short wedge that is mostly inner ring is the one to look at.

The grid

The error log grid
The class chip carries the verdict, and the Gap column turns a wall of identical backup lines into a rhythm you can scan.
Column What it holds
Time Shows the date as well when the log spans more than a day.
Gap Time since the previous row on screen. Bursts read as bursts.
Source The log’s own errproc – a SPID, Logon, Backup, Server.
Class The verdict, with the error number where the line carries one. Fatal and Error are filled chips; Routine and Info are outlined, because they are context rather than a verdict.
Message The line itself.

The left edge of every row carries the class as a stripe, so the worst rows are findable running your eye down the column without reading anything.

Group swaps the grid for one row per distinct message: count, first seen, last seen, class, and the message. Two hundred and thirty-one identical failed logins become one row that says 231.

The detail pane

Double-click a row – or a tick on the chart – and the pane opens on the right with:

  • the timestamp, the source, and the error number, severity and state parsed out of the text;
  • the lines around it, from the log in file order, with the one you opened marked ►;
  • what it means, for the errors that earn an explanation;
  • Previous / Next, which walk the grid without closing the pane.

The context is the reason the pane exists. Error: 825, Severity: 10, State: 2. is a complete log entry that says nothing at all; the sentence explaining it is the next line down. Stack dumps, failed-login blocks and CHECKDB output are all several consecutive lines.

The toolbar

Control What it does
◀ / ▶ and the picker Which retained log to read. Higher archive numbers are older.
Server / Agent The SQL Server error log or the SQL Server Agent log.
All / 7d / 24h How far back to read. Applied by SQL Server, not after the fact.
Find A search term, passed to the procedure. Press Enter. Also runs on the server.
Hide routine Hides the log’s background noise. Instant – it does not re-read the log.
Group One row per distinct message instead of one row per line.
Cycle log Closes the current log file and shifts every archive number up by one.
Refresh Reads the log again.

The window and the search are the performance controls. Both are passed to xp_readerrorlog, so SQL Server does the filtering rather than sending several hundred thousand rows across the wire first. On an instance whose log has never been cycled, the difference is substantial – which is why the window defaults to seven days rather than the whole file.

What the classes mean

Class What lands in it
Fatal Severity 20–25, and anything that produced a stack dump or an assertion.
Error Severity 17–19 – resource exhaustion and internal errors, not user mistakes.
Warning I/O taking longer than 15 seconds, configuration changes, databases going into recovery.
Security Logins, permissions and audit.
Routine Log backups, clean CHECKDBs, the copyright banner, startup messages.
Info Everything else.

Security is its own class rather than a severity band on purpose. SQL Server writes a failed login at severity 14 – the same severity as a query against a table that does not exist. Sorting purely on severity puts the single most security-relevant line in the log in with the typos.

What to look for

Error 825. A read that succeeded, but only after failing and being retried. Nothing failed visibly, which is why it gets missed – it is the warning that comes before 823 and 824. The storage is already returning bad data intermittently.

A cluster of 18456s from one account in one minute. Check the state number in the pane: state 8 is a wrong password, state 5 is an account that does not exist, state 11 or 12 is a valid login being denied server access. They need different fixes.

A storage lane and a security lane lighting up in the same column. Two symptoms, one cause – usually the box, not SQL Server.

A Log covers tile reading months. The log has not been cycled in a long time. Every read of it is slow, and finding anything in it is worse. Cycle log, and consider a weekly agent job to do it.

Nothing in the Routine tile. Either the instance really is quiet, or something is very wrong with what is being logged.

Permissions

Feature Needs
Reading a log EXECUTE on xp_readerrorlog.
The archive picker EXECUTE on xp_enumerrorlogs – typically sysadmin or securityadmin.
Cycle log sysadmin.

If the archives cannot be listed, the picker collapses to Current, the arrows go dead, and an amber band says why. The page is never worse than it would have been without the picker at all.

Amazon RDS is detected automatically and read through rdsadmin.dbo.rds_read_error_log with the same arguments. Cycling is not available on RDS.

Report Why you would go there
Failed Jobs Whether an agent job failed at the moment the log went loud.
Job History Everything the agent was running at that timestamp.
Email Alert Log Whether anyone was told.
Last DBCC CHECKDB Known Good After anything in the 823/824/825 family.
Backup Status After error 3041 or a backup device error.
Linked Servers For connection and authentication errors that involve another instance.

Frequently asked questions

Why does the whole log take so long to read? Because it is the whole log. xp_readerrorlog reads the file. On an instance that has never cycled its log, that file holds everything since the last restart. Use the window or the search – both are applied by SQL Server before anything crosses the wire.

Why is the default seven days and not everything? Because the default has to be the one that is fast on the worst case, and the worst case is an uncycled log. Switch to All whenever you want it.

Higher archive numbers are older? Yes. Archive 1 is the log before the current one, archive 2 the one before that. Cycling shifts every number up by one – what was current becomes archive 1.

Where did the failed logins go? They used to be hidden. They used to be hidden, and that was a defect. The old filter listed Login failed for user% among “common benign messages”. Failed logins now have their own class and are never hidden by Hide routine.

Why is there no row number column any more? It was the ordinal of a sorted list, and it changed whenever you sorted a column.

Why does the Message column not resize to fit? Because one 4000-character line would set the width for the rest of the session. The full text is in the detail pane, where it can wrap.

Does the page refresh itself? No. An error log changes when something happens. A sixty-second heartbeat would re-read the file to redraw the same picture.

Can I see history from before the oldest archive? Not from this page. SQL Server keeps a fixed number of archives – six by default – and once one is aged out it is gone. Raising that count is a server setting, under Management → SQL Server Logs → Configure in SSMS.