Reading the Error Log With T-SQL

A green dashboard can sit beside an error log full of clues. Reading the error log with T-SQL lets you search those clues without paging through a crowded SSMS window.

Know Which Log You Are Reading

SQL Server writes engine messages to its error log. SQL Agent has a separate log. The current engine log is archive zero; older numbered archives hold earlier periods. A service restart normally creates a new current log. If you only search archive zero after a restart, you can miss the event that caused the trouble.

I check the log boundary before saying an error is absent. The message can be one file older than expected. Record the time of the incident and the server’s restart history, then search the relevant archives. Error logs are operational evidence, but retention is limited. Plan their storage and cycling with that in mind.

Read the Current Engine Log

sp_readerrorlog returns LogDate, ProcessInfo, and Text. Its first parameter selects the archive number, and the second selects the SQL Server or SQL Agent log. The example reads the current SQL Server log. Run it with the permissions required by your version and environment. A busy log produces many rows, so add filters for routine investigation.

I keep this simple way of reading the error log with T-SQL in a runbook because it works when the SSMS viewer is slow or the interface is unavailable. It is also easy to save the result with an incident note. Do not mistake a successful procedure call for a complete search. It only covers the archive you selected.

EXEC sys.sp_readerrorlog
    @p1 = 0,
    @p2 = 1;

Reading the Error Log With T-SQL for a Specific Message

The third parameter filters for a text string. Use a stable term from the reported problem, such as backup, error, or a database name. Broad words can return noise; narrow words can miss alternate phrasing. Try more than one search when the cause is not yet known. The fourth parameter adds a second text filter when you need it.

The example searches current engine messages containing backup. Replace the term with the one that fits your investigation. A successful backup message and a failed backup message both match, so read the full text and timestamps. Search is a way to narrow reading, not a verdict.

EXEC sys.sp_readerrorlog
    @p1 = 0,
    @p2 = 1,
    @p3 = N'backup';

Reading Older Error Log Archives With T-SQL

Pass one for the previous log, two for the one before that, and so on. Use the error log viewer or the log inventory procedure to see which archives exist. The archive count depends on server configuration and cycling. A server with frequent restarts can consume its retained slots quickly. This is why an incident timeline should include restarts.

I search around the reported time, then move across archive boundaries as needed. If a message is not present, state which archives and terms you checked. The query below reads the previous engine log. It will return an error if that archive does not exist.

EXEC sys.sp_readerrorlog
    @p1 = 1,
    @p2 = 1;

Filter a Time Window When Reading the Error Log With T-SQL

sp_readerrorlog accepts archive and text filters, not start and end time parameters. Read a relevant archive into a temporary table if you need a SQL time filter, or narrow the search by message text first. Confirm that the server clock and incident timestamps use compatible time zones. A mismatch between application and server time can hide the right interval in plain sight.

I start with a wider window, then narrow it after seeing the pattern. Overly tight filters are a common reason to report no result. Include time before the first user complaint because an engine warning can precede the visible symptom. Record the archive number and exact window so another DBA can reproduce the search. For a busy log, use a specific text term before loading rows into a temporary table.

Cycle for Manageable Files

sp_cycle_errorlog closes the current engine error log and starts a new one. It does not restart SQL Server. A planned cycle schedule keeps files manageable and creates predictable archive boundaries. Review the configured number of retained files so cycling does not overwrite the period you still need. Back up incident evidence through the approved process before an aggressive cycle.

I do not cycle in the middle of an investigation without noting it. The action changes where new messages appear and can confuse someone reading archive zero. A schedule should reflect log volume and retention needs. This is operations housekeeping, not a fix for errors inside the log.

EXEC sys.sp_cycle_errorlog;

Read Messages in Context

An error log contains informational startup messages, database recovery notices, backup messages, and serious failures. Severity depends on the message and surrounding evidence. Look for repeated patterns, related database names, and preceding warnings. A single line copied into a chat can lose the sequence that explains it.

I correlate log times with job history, Windows events, and application complaints. If an I/O error appears, involve the storage team with the exact time and file path. If a login failure appears, inspect the state and source before changing credentials. The log points to the next question. It rarely supplies the full answer by itself.

Separate Agent Problems

SQL Agent has its own error log and job history. A failed job can leave detail in the job step output while the engine log remains quiet. When a report says a scheduled task failed, check Agent history and the Agent log as well as the SQL Server log. The second sp_readerrorlog parameter selects the Agent log when set to two.

I prefer to start with the system that reported the problem. For a job failure, inspect the step message first, then search the engine log around the same time. This avoids reading an enormous log for a problem whose error is already in msdb. One query source does not have a monopoly on truth.

Make Search Results Actionable

Record the instance, archive number, search terms, time window, matching messages, and next owner. If no message appears, say what you searched and what evidence is still missing. Set alerts for important recurring errors rather than relying on manual reading alone. Test the alert route so someone receives it.

What message would you wish you had noticed yesterday? Use that question to shape monitoring. Reading the error log with T-SQL is an investigation skill. Routing key errors before an incident is a reliability practice. Both need a clear retention plan so the evidence remains available when you need it.

Related reading on this blog: T-SQL Script: How to Search for Multiple Values in ERRORLOG? and How to Increase Number of Errorlog Files.

An error log is not a diagnosis by itself, it is a dated record that points the investigation.

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