SQL Server Error Log Search Script: xp_readerrorlog

calendar Apr 8, 2026 / · 9 min read · sql server sql scripts xp_readerrorlog sp_readerrorlog error log database administration monitoring diagnostics maintenance scripts sys.sp_enumerrorlogs  ·
Share on: linkedin copy

A database goes suspect at 2 a.m., a backup job reports failure, or one login is being refused again and again, and the answer is a line in the SQL Server error log. This script finds that line from T-SQL: it filters by keyword, narrows by time window, reads archived logs and loads the result into a temp table for further filtering. One of the procedures it relies on is undocumented, which changes how it should be used.

Purpose and Overview

Error log triage usually starts with a vague symptom and a wide time range. Loading a large log into the SSMS Log Viewer is slow, and the Viewer cannot be scheduled. Reading the log from a query turns the same search into something that can run from a SQL Agent job, a monitoring script or a remote session.

Two procedures do the reading, and they are not equals. sys.sp_readerrorlog is documented. It takes four arguments: the log number, the product (SQL Server or SQL Server Agent), a first search string and a second search string that refines the first. xp_readerrorlog is the extended stored procedure underneath it, and Microsoft publishes no reference page for it. It accepts the same four arguments plus a start time, an end time and a sort order, so a date window and DESC ordering only work when the call goes to xp_readerrorlog directly. The documented syntax of sys.sp_readerrorlog stops at four arguments, so a seven-argument call belongs on xp_readerrorlog.

Because xp_readerrorlog is undocumented, its parameter list here comes from the community write-ups listed in References, not from a vendor specification. It is long-standing and widely used, but Microsoft is free to change or remove it. Use sys.sp_readerrorlog when four arguments are enough, and reserve xp_readerrorlog for the time window and the sort order.

Code Breakdown

The script has five steps: list the logs, run a simple search with the documented wrapper, run a filtered search with the extended procedure, capture rows into a temp table and sweep the archives. Run them one at a time.

 1-- 1. List the available SQL Server error logs and when each was initialized
 2EXEC sys.sp_enumerrorlogs;
 3
 4-- 2. Documented wrapper: current log (0), SQL Server log (1), one search string
 5EXEC sys.sp_readerrorlog 0, 1, N'error';
 6
 7-- 3. xp_readerrorlog: two strings (both must match), a 24-hour window, newest first
 8DECLARE @Start DATETIME2(3) = DATEADD(HOUR, -24, GETDATE());
 9DECLARE @End   DATETIME2(3) = GETDATE();
10
11EXEC master.sys.xp_readerrorlog
12     0,                 -- log number: 0 = current
13     1,                 -- product: 1 = SQL Server, 2 = SQL Server Agent
14     N'Login',          -- search string 1
15     N'failed',         -- search string 2 (must also be present)
16     @Start,            -- start of window
17     @End,              -- end of window
18     N'desc';           -- sort order: asc or desc
19
20-- 4. Capture rows into a temp table and filter further with ordinary T-SQL
21IF OBJECT_ID('tempdb..#ErrorLog', 'U') IS NOT NULL
22    DROP TABLE #ErrorLog;
23
24CREATE TABLE #ErrorLog
25(
26    LogDate     DATETIME2(3),
27    ProcessInfo NVARCHAR(50),
28    [Text]      NVARCHAR(MAX)
29);
30
31INSERT INTO #ErrorLog (LogDate, ProcessInfo, [Text])
32EXEC master.sys.xp_readerrorlog 0, 1, N'Login', N'fail', @Start, @End;
33
34SELECT   LogDate, ProcessInfo, [Text]
35FROM     #ErrorLog
36WHERE    ProcessInfo = N'Logon'
37  AND    [Text] NOT LIKE N'%Login lacks Connect SQL permission%'
38ORDER BY LogDate DESC;
39
40-- 5. Sweep the archives: set @MaxArchive to the highest Archive number step 1 listed
41DECLARE @Archive    INT = 0;
42DECLARE @MaxArchive INT = 6;
43
44TRUNCATE TABLE #ErrorLog;
45
46WHILE @Archive <= @MaxArchive
47BEGIN
48    INSERT INTO #ErrorLog (LogDate, ProcessInfo, [Text])
49    EXEC master.sys.xp_readerrorlog @Archive, 1, N'Login', N'failed';
50
51    SET @Archive += 1;
52END;
53
54SELECT   LogDate, ProcessInfo, [Text]
55FROM     #ErrorLog
56ORDER BY LogDate DESC;

Step 1: sys.sp_enumerrorlogs

sys.sp_enumerrorlogs is also undocumented. It lists the error log files the instance currently holds and when each was initialized, which tells you how far back the search can reach before any text is read. Run it first, with no argument, because the highest archive number it shows is the value for @MaxArchive in step 5.

Step 2: The documented wrapper

sys.sp_readerrorlog 0, 1, N'error' reads the current log. Log number 0 is the current file, 1 is ERRORLOG.1, 2 is ERRORLOG.2 and so on. Every time SQL Server starts, the current log is renamed to ERRORLOG.1, the previous ERRORLOG.1 becomes ERRORLOG.2, and each older file moves up one number, as long as the files exist. The product argument defaults to SQL Server when omitted, and the two string arguments are nvarchar(4000).

Permissions follow the version. On SQL Server 2019 and earlier the caller needs VIEW SERVER STATE. On SQL Server 2022 and later the caller needs VIEW ANY ERROR LOG or VIEW SERVER PERFORMANCE STATE. Those requirements are documented for the wrapper only; the requirements of the extended procedure itself are not published.

Step 3: Window and sort with xp_readerrorlog

The first four arguments match the wrapper. The fifth and sixth are the start and end of the window, and the seventh is the sort, either asc or desc. The two search strings combine with AND, so the call above returns only rows that contain both Login and failed. An OR is two calls whose results are combined in a temp table.

The search strings must be Unicode. Pass N'Login', not 'Login'; a plain varchar literal is a typing mistake that is easy to make and easy to miss when copying from an older script. Published examples pass an empty string for a string slot they do not want to filter on, which is how a call supplies a second filter without a first.

Step 4: Capturing to a temp table

INSERT ... EXEC loads the output into #ErrorLog. The result set has three columns: the time of the entry, the process that wrote it and the message text. Once the rows are in a table, the filter language is plain T-SQL. The example keeps only rows whose process is Logon and drops one known pattern with NOT LIKE. The same table can be grouped by the hour, joined to a list of known client addresses or counted per message text. Text is bracketed because it is also a data type name.

Step 5: Sweeping archived logs

The loop passes the log number as a variable, so one temp table collects matches from ERRORLOG through ERRORLOG.6. Six is the default number of archives that SQL Server keeps in addition to the current file, so the loop covers a default instance without any change. If the retention setting was changed, set @MaxArchive from the step 1 listing instead of the default. Do not rely on the loop to run past the last file: how a call behaves for a number that does not exist has not been tested for this script, so keep the upper bound to what the listing shows.

Key Benefits and Use Cases

  • Search without the Log Viewer. The filter applies before rows come back, so a broad question such as every message containing Login and failed returns only those rows.
  • Time windows for scheduled checks. A job that runs hourly can pass the last hour as the window and report only new entries, instead of re-reading the same lines every run.
  • Reach into history. The archive loop answers whether an error is new or has been repeating for weeks, as far back as the retained logs go.
  • Agent log access. Product 2 reads the SQL Server Agent error log with the same arguments, which makes job startup and failure messages searchable the same way.
  • Security review. Failed-login entries carry the process label Logon, which makes them easy to isolate, count and group once they are in a temp table.
  • No file system access. The search needs no xp_cmdshell and no access to the server's disk, so it works from any session with the right permission.

Performance Considerations

  • Large current log. A log file that has grown very large takes longer to read. The start and end arguments restrict the rows returned, and cycling the log keeps individual files smaller.
  • Cycling the log. EXEC sp_cycle_errorlog closes the current log file and cycles the numbering the way a restart does, without stopping the server. The new log starts with version and copyright information and a line stating that the new log was created. The procedure returns 0 on success and 1 on failure, and only members of the sysadmin fixed server role can run it. The Agent log has its own procedure, sp_cycle_agent_errorlog.
  • Retention. The default is six archived files, not counting the current one. The limit and a maximum size per file in KB are set in SSMS under Management, SQL Server Logs, Configure. A file size of 0 means the log has no size limit.
  • Undocumented interface. Because xp_readerrorlog has no vendor reference page, a new release could change its arguments or output. Keep calls to it in one place, such as one script or one wrapper procedure, so that a change means one edit. Use sys.sp_readerrorlog wherever four arguments suffice.
  • Temp table size. An unfiltered capture of a busy log can produce a large #ErrorLog. Filter in the call with the search strings and the window before filtering again in T-SQL.

Practical Tips

  • Start with the listing. Run sys.sp_enumerrorlogs, note the oldest archive and its date, and only then decide how many logs to read.
  • Use a window for repeated checks. Compute @Start from the previous run's time instead of a fixed number of hours so no entry is skipped or read twice.
  • Check the clock. Compare the window you passed with a known entry, such as the startup message, before trusting a time-bounded search for a negative result.
  • Search one string, then refine. Begin with a single word like error, then add the second string once you have seen how the message is worded on your version.
  • Read the Agent log separately. EXEC sys.sp_readerrorlog 0, 2, N'failed' searches the Agent log for failed job messages.
  • Persist what matters. A nightly job that inserts the prior day's filtered rows into a monitoring table keeps history past the retention window and makes trends queryable.
  • Cycle on a schedule. Cycling the log weekly, or when a file passes a size you choose, keeps each file short enough to search quickly.

Conclusion

xp_readerrorlog adds a time window and a sort order to the four arguments of sys.sp_readerrorlog, and a temp table adds the rest of T-SQL on top. Together they turn the error log from a file to scroll into a table to query. Treat the extended procedure as undocumented, keep it behind one script, and fall back to the documented wrapper when four arguments are enough.

References

Posts in this series