Months of routine entries can hide the error message needed for an investigation. Searching the error log by date and text reduces that noise while preserving the timeline needed for diagnosis.
Identify the Log before Narrowing the Search
SQL Server maintains a current database engine error log and retained archives. SQL Server Agent has its own log. Choose the correct source before concluding that a message is missing.
Log number zero selects the current file, and higher numbers select older archives. Log type one selects the engine, while type two selects Agent. These numbers describe the requested file and component, rather than severity levels.
I record the instance and archive number beside every extracted message. I also preserve the search interval in the incident notes. A copied sentence without its source and time makes a surprisingly unhelpful souvenir.
The examples target SQL Server on Windows, including SQL Server 2025. Run them using an account authorized to read the selected logs. Azure SQL Database does not expose the same instance error-log investigation interface.
SELECT CONVERT(nvarchar(128), SERVERPROPERTY('ServerName')) AS ServerName,
SYSDATETIME() AS CapturedLocal;
EXEC master.sys.sp_enumerrorlogs;
EXEC master.sys.sp_readerrorlog @p1 = 0, @p2 = 1;Inspect the retained archive list before choosing a number. Restarting or cycling the log moves existing archive numbers. Archive number two in this week's incident notes can point to a different file next week.
Supply the Extended Search Parameters in Order
xp_readerrorlog accepts the archive number, log type, two text filters, start time, end time, and sort direction. Use typed variables for the timestamps and Unicode strings for the text. The documented sp_readerrorlog wrapper exposes a smaller text-search interface.
DECLARE @StartTime datetime = '2026-09-25T08:00:00';
DECLARE @EndTime datetime = '2026-09-25T10:00:00';
EXEC master.sys.xp_readerrorlog
0, 1,
N'Login failed',
N'SalesApp',
@StartTime,
@EndTime,
N'ASC';Both supplied strings must match the same log row. This search therefore finds messages containing both Login failed and SalesApp. Passing NULL for either filter removes that particular text requirement.
Use ASC to inspect a sequence from its beginning and DESC to start with the newest matches. Sorting the returned messages does not change their timestamps. Read surrounding context when a single filtered line leaves the cause ambiguous.
The extended date parameters are less formally documented than the wrapper's public text parameters. Verify the call against your supported SQL Server build before embedding it in operational automation. Preserve an alternative using captured rows and ordinary T-SQL filtering.
Search the Error Log by Date with a Matching Time Window
Error-log timestamps follow the instance's local time context. Convert an incident reported in another time zone before supplying the interval. Record the original zone and conversion, especially near daylight-saving transitions.
A date without a time represents midnight, which can accidentally exclude the useful part of an incident. Provide explicit timestamp values with unambiguous formatting. Avoid converting timestamps into formatted strings merely to compare dates.
Search a slightly wider interval when the application and server clocks differ. Then narrow the captured rows using explained boundaries. A wider search is useful evidence gathering, rather than permission to silently change the incident period.
Do not assume an empty search proves no failure occurred. The message can be in another archive, another component, or another server. Its wording can also differ from the text you selected.
Capture the Error Log by Date for Repeatable Filtering
A temporary table lets you apply additional filters without rereading the file for every variation. The expected result has timestamp, process information, and message text columns. Store the message as a large Unicode value to avoid truncating useful context.
DROP TABLE IF EXISTS #ErrorRows;
CREATE TABLE #ErrorRows
(
LogDate datetime NULL,
ProcessInfo nvarchar(50) NULL,
MessageText nvarchar(max) NULL
);
DECLARE @StartTime datetime = '2026-09-25T08:00:00';
DECLARE @EndTime datetime = '2026-09-25T10:00:00';
INSERT #ErrorRows(LogDate, ProcessInfo, MessageText)
EXEC master.sys.xp_readerrorlog
0, 1, NULL, NULL, @StartTime, @EndTime, N'ASC';
SELECT LogDate, ProcessInfo, MessageText
FROM #ErrorRows
WHERE LogDate >= @StartTime
AND LogDate < @EndTime
AND (MessageText LIKE N'%Login failed%'
OR MessageText LIKE N'%Error: 18456%')
ORDER BY LogDate, ProcessInfo;The outer predicate explicitly defines a half-open interval. This avoids relying on the extended procedure's boundary behavior for a precise report. The OR condition also differs from the procedure's two simultaneous text filters.
Keep the capture window reasonably bounded when a log is large. Reading every archive into one temporary table can add unnecessary work during an incident. Capture only the files and intervals needed to answer the question.
An INSERT EXEC capture has the usual nesting limitation. Do not place this operation inside another INSERT EXEC chain without testing the calling structure. A standalone diagnostic batch keeps that constraint visible.
Search Archives without Mixing Their Identities
If the interval overlaps a restart or log cycle, inspect the neighboring archives too. Capture one archive at a time and tag exported results with its number. Keep the extraction timestamp because archive numbers can move later.
A startup message concerns engine initialization, while a new current log can result from manual cycling. Those are different events even when they share similar header information. Check the startup metadata when the distinction matters.
Messages with identical timestamps can be separate rows. Do not remove duplicates solely because a spreadsheet displays the same second twice. Preserve process information and complete message text when analyzing related entries.
An archive can already be unavailable under the retention configuration. Say that the required historical evidence is missing rather than inventing a clean history. Improve retention for future investigations through the approved operating procedure.
Cycle the Log as Maintenance, Not Diagnosis
The following command closes the current engine log and starts a new one. It requires sysadmin membership and is a maintenance action. Use it after preserving required evidence and reviewing archive retention.
-- Maintenance action: run only in the approved log rotation window.
EXEC master.sys.sp_cycle_errorlog;Cycling does not restart the engine and does not repair the underlying error. Repeated cycling can age useful archives out of retention. Establish a deliberate rotation schedule instead of cycling whenever a search feels inconvenient.
SQL Server Agent log cycling uses its separate procedure. Confirm the component before performing either action. Read-only searching should remain separate from the decision to rotate logs.
Turn an Error Log by Date Search into a Finding
Can another administrator reproduce your error log by date search using the recorded server, archive, and timestamps? Include those details with the extracted rows. State whether the text predicates required both phrases or accepted either phrase.
For SQL Server 2022 and later, error-log access accepts VIEW ANY ERROR LOG or VIEW SERVER PERFORMANCE STATE. Earlier supported versions use VIEW SERVER STATE for the documented reader. Request suitable access without granting maintenance privileges merely to read evidence.
Finish by relating the messages to the request, login, database, or recovery operation under investigation. A successful error log by date search finds relevant lines; it does not establish every cause automatically. Preserve context before recommending a corrective change.
Related reading on this blog: T-SQL Script: How to Search for Multiple Values in ERRORLOG? and Recycling the Error Log on a Schedule and Keeping Enough History.
A filtered error log is not the whole incident, it is a reproducible slice of evidence for explaining the incident.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
