Reading the SQL Server Error Log Properly

The SQL Server error log records much more than errors, including startup details and important engine events. Read it around the problem’s time, keep related messages together, and retain enough history for later investigation.

Use the Right Log

The Database Engine error log and the SQL Server Agent log are different sources. Windows event logs add another layer. Start with the engine log for engine startup and database messages. Use Agent history and its log for scheduled-job execution questions.

Don’t confuse the error log with a database transaction log. The transaction log supports recovery and records database changes in its own format. The error log is readable operational output. Reading one doesn’t replace examining the other for its proper purpose.

EXEC sys.sp_readerrorlog 0, 1;

The first argument selects the current archive number, with zero representing the active file. The second selects the Database Engine log. Run with the documented permissions for your SQL Server release. A permission failure doesn’t mean there are no relevant messages.

Read Startup as an Inventory

Startup messages can tell you the build, startup parameters, detected resources, and service initialization details. They provide useful context when an instance restarted unexpectedly or configuration differs from what the team remembers. Preserve the relevant sequence rather than copying one line in isolation.

EXEC sys.sp_readerrorlog 0, 1, N'Microsoft SQL Server';

EXEC sys.sp_readerrorlog 0, 1, N'Server process ID';

These searches illustrate narrowing the log by text. Exact wording varies across releases and events. If a search returns nothing, inspect the unfiltered period or the previous log. The startup message can belong to an older archive after a manual cycle.

Compare startup evidence with the current service and configuration record. A command-line startup parameter can explain behavior not obvious in an application setting. Keep the instance identity attached to the saved lines, especially when several instances share a Windows server.

Search by Time and Symptom

Begin with the incident timestamp and inspect the surrounding messages in the SSMS log viewer. Account for time zones when comparing application logs. Then search for a specific database or error phrase. Narrowing too early can hide the preceding operating system error that explains the failure.

EXEC sys.sp_readerrorlog 0, 1, N'Error';

EXEC sys.sp_readerrorlog 0, 1, N'Login failed';

Text searches are convenient filters, not a complete classification of every problem. Some important messages don’t contain the word Error. Read adjacent entries when you find a candidate. Keep error number, severity, state, and any embedded operating system details.

A generic connection failure shown to a client can have more useful state information in the server log. Match the time and account carefully. Don’t assume the nearest login failure belongs to your application when the server handles many clients.

Check Retained Archives

EXEC sys.sp_readerrorlog 1, 1;

This reads the previous engine error log when that archive exists. Review the configured number of retained files and how frequently they cycle. A busy system can lose the period you need sooner than expected. Retention measured in files isn’t automatically retention measured in days.

Preserve relevant archives before changing retention or cycling repeatedly during an incident. An exported log can contain server names, paths, and login information. Store it in an approved location and redact it before sharing outside the responsible team.

Also inspect the Agent log when the incident concerns a job rather than the engine. Keep the sources distinct in your report. Combining unrelated lines without their source and timestamp makes the investigation harder to follow.

Cycle Without Restarting the Engine

SQL Server normally cycles its error log when the service starts. You can also cycle the engine log with sp_cycle_errorlog without restarting SQL Server. Schedule rotation according to activity and retention needs. There is no benefit in restarting the service merely to get a smaller log file.

-- Run only when planned log rotation is intended.
EXEC sys.sp_cycle_errorlog;

Cycling starts a new active file and advances the archive sequence. It isn’t a backup of every old log. When the retention limit is reached, older history can be removed. Confirm retention and preserve incident evidence before performing the operation.

A regular rotation schedule can make the files easier to navigate. It should also leave enough history to cover the time between an event and its discovery. Choose that period from operational needs rather than using a schedule because another server uses it.

Treat the Log as One Part of the Evidence

The error log doesn’t record every query or every row change. It cannot retrospectively become a complete audit trail. For performance investigations, combine its messages with requests, waits, Query Store, and application context. For accountability, configure the appropriate audit before the event.

Finish with the exact messages and a short explanation of what they establish. Separate documented meaning from your current hypothesis. I want the log to guide the next check, rather than supply alarming phrases copied into an email without context.

The error log is not a record of everything, it is a timeline of engine events worth reading in context.

This post was rewritten from scratch in September 2026. The original, published on 2008-02-21, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.