SQL Server Error Log Search Script: xp_readerrorlog

Search the SQL Server Error Log with xp_readerrorlog

This script reads and filters the SQL Server error log using xp_readerrorlog and sys.sp_readerrorlog, supporting keyword search, date range filtering, and access to archived log files — all from T-SQL without touching the file system.

Purpose and Overview

The SQL Server error log records startup events, login failures, database state changes, backup completions, DBCC output, and any errors raised by the engine. When something goes wrong — a database goes suspect, a backup fails, or a login is repeatedly denied — the error log is the first place to look. SQL Server provides two built-in mechanisms for reading the log from T-SQL: the undocumented extended stored procedure xp_readerrorlog and its documented wrapper sys.sp_readerrorlog. Both support keyword filtering and date ranges, making them far more useful than opening the log file in SSMS's Log Viewer for scripted or automated diagnostic work. This script, adapted from the Read_errorlog.sql in the sqlserver-kit collection by Konstantin Taranov, demonstrates how to search one or more error log files efficiently from T-SQL.

Code Breakdown

The complete script is shown below, followed by an explanation of each section.

 1-- 1. List all available error log files (current + archives)
 2EXEC sys.sp_enumerrorlogs;
 3
 4-- 2. Read the current error log (log 0), search for a keyword
 5EXEC sys.sp_readerrorlog
 6    0,          -- log file number: 0 = current, 1 = most recent archive, etc.
 7    1,          -- log type: 1 = SQL Server error log, 2 = SQL Server Agent log
 8    N'error';   -- search string 1 (optional)
 9
10-- 3. Read with two keyword filters (AND logic) and a date range
11EXEC sys.sp_readerrorlog
12    0,                          -- log file number
13    1,                          -- log type
14    N'Login failed',            -- search string 1
15    NULL,                       -- search string 2 (NULL = no second filter)
16    '2026-04-01 00:00:00',      -- start date/time
17    '2026-04-13 23:59:59',      -- end date/time
18    N'desc';                    -- sort: 'asc' or 'desc'
19
20-- 4. Capture results into a temp table for further filtering
21IF OBJECT_ID('tempdb..#ErrorLog', 'U') IS NOT NULL
22    DROP TABLE #ErrorLog;
23
24CREATE TABLE #ErrorLog
25(
26    LogDate     DATETIME,
27    ProcessInfo VARCHAR(50),
28    Text        NVARCHAR(MAX)
29);
30
31INSERT INTO #ErrorLog (LogDate, ProcessInfo, Text)
32EXEC sys.sp_readerrorlog 0, 1;
33
34SELECT *
35FROM #ErrorLog
36WHERE Text LIKE N'%error%'
37    AND Text NOT LIKE N'%This is an informational message%'
38ORDER BY LogDate DESC;

sys.sp_enumerrorlogs

sys.sp_enumerrorlogs returns a list of all error log files currently available on the instance — the active log and any archives SQL Server has kept. The result set includes:

• Archive — the log file number (0 = current, 1 = most recent archive, and so on)
• Date — when SQL Server last wrote to this log file (for archives, approximately when it was rolled over)
• Log File Size (Byte) — file size in bytes

Run this first to see how many archived logs are available and what date range they cover before searching them. SQL Server maintains up to six archived logs by default; this limit is configurable under SQL Server Agent Properties or via xp_instance_regwrite.

sp_readerrorlog Parameters

sys.sp_readerrorlog (and the underlying xp_readerrorlog) accepts up to seven parameters:

When both search strings are provided, SQL Server returns only rows containing both terms — an AND filter, not an OR. To simulate an OR, run the query twice or capture results to a temp table and union.

Output Columns

All calls return three columns:

• LogDate — the timestamp of the log entry
• ProcessInfo — the source of the entry: Server, spid12, Backup, DBCC, Logon, etc.
• Text — the full log message text

Capturing to a Temp Table

The INSERT INTO #ErrorLog ... EXEC sys.sp_readerrorlog pattern captures the log output into a temp table for additional T-SQL filtering. This is useful when the built-in search string filter is not expressive enough — for example, to apply NOT LIKE conditions to exclude noisy informational messages, or to join with other data sources.

Key Benefits and Use Cases

• Searches the active error log and any archived log files without leaving T-SQL
• Keyword and date range filters reduce result volume immediately, unlike SSMS Log Viewer which loads everything first
• Agent log access (log type 2) exposes SQL Server Agent startup, shutdown, and job outcome messages
• Results can be captured to a temp table for complex filtering, aggregation, or export
• sp_enumerrorlogs gives a quick map of log archive coverage before deciding which file to search
• No file system access, no xp_cmdshell, no SSMS required — runs in any T-SQL client

Performance Considerations

• Log file size: Reading a very large error log file can take several seconds. If the current log has grown large (check the byte size from sp_enumerrorlogs), use the date range parameters to restrict the rows returned. Cycle the log regularly with EXEC sp_cycle_errorlog to keep individual file sizes manageable.
• sp_cycle_errorlog: Running EXEC sp_cycle_errorlog forces SQL Server to close the current error log and open a new one, archiving the current file. This is safe to run at any time and is often scheduled weekly to prevent the active log from becoming very large.
• Number of archived logs: SQL Server keeps six archived logs by default. You can increase this to up to 99 under SQL Server Properties > Advanced > Number of Error Log Files. More archives means more history available for search — especially useful when investigating events that occurred days ago.
• xp_readerrorlog vs sp_readerrorlog: xp_readerrorlog is undocumented and its parameter interface could theoretically change between SQL Server versions. sys.sp_readerrorlog is the documented wrapper and is preferred for production scripts. Both currently behave identically.

Practical Tips

To search all archived log files in a loop and combine results, use a cursor driven by sp_enumerrorlogs:

 1IF OBJECT_ID('tempdb..#AllLogs', 'U') IS NOT NULL
 2    DROP TABLE #AllLogs;
 3
 4CREATE TABLE #AllLogs
 5(
 6    LogDate     DATETIME,
 7    ProcessInfo VARCHAR(50),
 8    Text        NVARCHAR(MAX)
 9);
10
11DECLARE @lognum   INT = 0;
12DECLARE @maxlog   INT;
13
14SELECT @maxlog = MAX(Archive) FROM sys.dm_os_ring_buffers
15    WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION'; -- fallback: just use 6
16
17SELECT @maxlog = MAX(Archive) FROM (
18    EXEC sys.sp_enumerrorlogs -- note: sp_enumerrorlogs cannot be used in a subquery directly
19) x;
20
21-- Simpler approach: loop through 0..6
22WHILE @lognum <= 6
23BEGIN
24    BEGIN TRY
25        INSERT INTO #AllLogs (LogDate, ProcessInfo, Text)
26        EXEC sys.sp_readerrorlog @lognum, 1, N'YourSearchTerm';
27    END TRY
28    BEGIN CATCH
29        -- log file does not exist; stop looping
30        BREAK;
31    END CATCH;
32
33    SET @lognum += 1;
34END
35
36SELECT * FROM #AllLogs ORDER BY LogDate DESC;

To find all login failure events in the last 24 hours, which is useful for security auditing:

1EXEC sys.sp_readerrorlog
2    0, 1, N'Login failed',
3    NULL,
4    DATEADD(HOUR, -24, GETDATE()),
5    GETDATE(),
6    N'desc';

To read the SQL Server Agent log instead of the SQL Server error log, change the second parameter from 1 to 2:

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

Schedule a daily SQL Server Agent job that captures the prior day's error log entries into a monitoring table. Flag any rows where Text LIKE '%error%' or ProcessInfo = 'Logon' with a high count, and send an alert email.

Conclusion

xp_readerrorlog and its documented wrapper sys.sp_readerrorlog give DBAs direct T-SQL access to the SQL Server error log and all its archives. The built-in keyword and date range filters make targeted searches fast and practical. For complex filtering — excluding noisy messages, combining multiple search terms, or joining log data with other tables — capture results to a temp table and apply standard T-SQL predicates. Keep archived log counts high enough to cover your investigation window, cycle the log regularly to control file size, and consider a scheduled job that builds a searchable error log history table for faster post-incident analysis.

References

• sqlserver-kit on GitHub by Konstantin Taranov — Source repository containing the Read_errorlog.sql script that inspired this article, originally from The Lone DBA blog
• Reading SQL Server Error Logs by The Lone DBA — Original article detailing the multi-file error log reading approach with noise filtering
• Microsoft Docs: xp_readerrorlog — Reference page for the extended stored procedure underlying sp_readerrorlog
• Microsoft Docs: sp_cycle_errorlog — Reference for the stored procedure that archives the current error log and opens a new one