Part 1 – How to Review the SQL Server Error Log like a DBA

The SQL Server Error Log is your first stop when something looks off. It records startup messages, configuration changes, critical errors, I/O problems, login failures, backup issues, and more. A fast daily review catches trouble before it becomes an outage.

Where to find the Error Log

In SSMS

1. Connect to the instance.
2. Expand Management.
3. Expand SQL Server Logs.
4. Double-click Current to open it. Use Filter in the toolbar to narrow by date or text.
5. Also check SQL Server Agent → Error Logs for Agent job issues.

On disk (default paths)

• SQL Server logs: ...\MSSQL\Log\ERRORLOG, ERRORLOG.1, ERRORLOG.2, etc.
• SQL Agent logs: ...\MSSQL\Log\SQLAGENT.OUT, SQLAGENT.1, etc.

Tip: Keep several rollover files and cycle them regularly so files don’t get huge:

EXEC sp_cycle_errorlog;           -- run weekly or monthly
EXEC sp_cycle_agent_errorlog;     -- for SQL Agent

Set the number of error logs to keep in SSMS: SQL Server Logs → right-click Configure.

What counts as Errors vs Warnings

• Errors: lines that include Error: with an error number and severity, failed operations, I/O failures, database or log full, corruption messages, service restarts that were not planned.
• Warnings: lines that include Warning:, frequent file autogrowth, long I/O warnings, SSPI handshake warnings, repeated login failures, failover and reconnect chatter that is unusual for your environment.

Top identifiers to scan every day

Prioritize these. If you see them, investigate immediately.

Critical errors

• 823, 824, 825: I/O problems. 825 is a “soft” retry warning, still serious.
• 9002: Transaction log full.
• 1101, 1105: File or filegroup is full.
• 3624: Assertion. Open a support case and collect dumps.
• 3456, 3013, 3041: Restore or backup failure.
• 824 plus DBCC output: Corruption. Run DBCC CHECKDB and plan repair.
• 17182, 17803, 701, 802: Memory, listener, or resource exhaustion.
• Always On: availability replica disconnected, failover, redo queue growing, synchronization suspension.

High-signal warnings

• Autogrow of file messages repeating. Right-size data and log files.
• Login failed for user 18456. A few is normal. Spikes or many from one host are not.
• SSPI handshake failed 17806. Kerberos or SPNs.
• Long I/O warnings like “I/O requests taking longer than 15 seconds”.
• Deadlocks: captured in the system_health XE session. If frequent, review.
• Agent job failures: check SQL Agent error log and job history.

How often should you review

• Daily at minimum for production.
• Consider an automated job every 15–60 minutes that scans the last time window and emails only when it finds issues.
• Track trends. A single autogrow might be fine. Five autogrows in a day is a capacity or sizing problem.

Simple manual filters you can run

You can read the current error log with xp_readerrorlog or sp_readerrorlog.

Last 24 hours, newest first:

DECLARE @Start DATETIME = DATEADD(HOUR, -24, GETDATE());
DECLARE @End   DATETIME = GETDATE();

-- 0 = current log, 1 = SQL Server log type, 'desc' = newest first
EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, @Start, @End, N'desc';

Only I/O related entries:

EXEC master.dbo.xp_readerrorlog 0, 1, N'I/O', NULL, NULL, NULL, N'desc';

Only login failures:

EXEC master.dbo.xp_readerrorlog 0, 1, N'Login failed', NULL, NULL, NULL, N'desc';

Only backup failures:

EXEC master.dbo.xp_readerrorlog 0, 1, N'Error: 3041', NULL, NULL, NULL, N'desc';

Daily checklist a DBA should follow

1. Open Current SQL Error Log. Filter to Last 24 hours.
2. Scan for 823, 824, 825, 9002, 1101, 1105, 3624, 3041 quickly.
3. Search for “Autogrow of file”. If frequent, right-size files and review growth settings.
4. Search for “Login failed”. If spikes or unusual sources, investigate.
5. Search for “SSPI handshake” and “I/O requests taking longer”.
6. If using Always On, search for “availability” and “replica” issues.
7. Check SQL Agent error log and any failed jobs.
8. Record findings in your daily log. Open tickets for anything not explained.

What to report as issues

• Any of the critical errors listed above.
• Repeated warnings that indicate a pattern (dozens of login failures, frequent autogrow, recurring SSPI, repeated long I/O).
• Backup failures or missing backups.
• Unplanned restarts, crash dumps, or assertion errors.
• Deadlocks that affect key workloads.

Optional: automate a daily HTML email of errors and warnings

This job reads the last 24 hours of the current error log, separates obvious Errors and Warnings, and emails an HTML table if anything is found.

Replace YourDatabaseMailProfile and ops@example.com. This uses only built-in objects and Database Mail.

-- Window to scan
DECLARE @Start DATETIME = DATEADD(HOUR, -24, GETDATE());
DECLARE @End   DATETIME = GETDATE();

IF OBJECT_ID('tempdb..#ErrorLog') IS NOT NULL DROP TABLE #ErrorLog;
CREATE TABLE #ErrorLog
(
    LogDate      DATETIME,
    ProcessInfo  NVARCHAR(50),
    [Text]       NVARCHAR(MAX)
);

-- Read current error log
INSERT INTO #ErrorLog
EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, @Start, @End, N'desc';

-- Classify rows
;WITH Classified AS
(
    SELECT
        LogDate,
        ProcessInfo,
        [Text],
        CASE
            WHEN [Text] LIKE 'Error:%'                                  THEN 'ERROR'
            WHEN [Text] LIKE '%Error: 823%' OR [Text] LIKE '%Error: 824%' OR [Text] LIKE '%Error: 825%' THEN 'ERROR'
            WHEN [Text] LIKE '%Error: 9002%' OR [Text] LIKE '%Error: 1101%' OR [Text] LIKE '%Error: 1105%' THEN 'ERROR'
            WHEN [Text] LIKE '%Error: 3041%' OR [Text] LIKE '%backup%failed%' THEN 'ERROR'
            WHEN [Text] LIKE '%assertion%' OR [Text] LIKE '%dump%'      THEN 'ERROR'
            WHEN [Text] LIKE 'Warning:%'                                 THEN 'WARNING'
            WHEN [Text] LIKE '%Autogrow of file%'                        THEN 'WARNING'
            WHEN [Text] LIKE '%Login failed for user%'                   THEN 'WARNING'
            WHEN [Text] LIKE '%SSPI handshake%'                          THEN 'WARNING'
            WHEN [Text] LIKE '%I/O requests taking longer%'              THEN 'WARNING'
            WHEN [Text] LIKE '%availability group%' OR [Text] LIKE '%availability replica%' THEN 'WARNING'
            ELSE 'INFO'
        END AS SeverityClass
    FROM #ErrorLog
)
SELECT * INTO #Errors   FROM Classified WHERE SeverityClass = 'ERROR';
SELECT * INTO #Warnings FROM Classified WHERE SeverityClass = 'WARNING';

-- Build simple HTML
DECLARE @html NVARCHAR(MAX) =
N'<html><body style="font-family:Segoe UI,Arial,sans-serif;">
<h2>SQL Server Error Log Summary (last 24 hours)</h2>';

IF EXISTS (SELECT 1 FROM #Errors)
BEGIN
    SET @html += N'<h3 style="color:#b00020;">Errors</h3>
    <table style="border-collapse:collapse;width:100%;" border="1" cellpadding="6">
    <tr style="background:#f6e5e5;"><th>Date</th><th>Process</th><th>Message</th></tr>' +
    CAST((
        SELECT
            td = CONVERT(VARCHAR(19), LogDate, 120), '',
            td = ProcessInfo, '',
            td = [Text], ''
        FROM #Errors
        ORDER BY LogDate DESC
        FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX)) + N'</table>';
END

IF EXISTS (SELECT 1 FROM #Warnings)
BEGIN
    SET @html += N'<h3 style="color:#8a6d3b;">Warnings</h3>
    <table style="border-collapse:collapse;width:100%;" border="1" cellpadding="6">
    <tr style="background:#fff6e5;"><th>Date</th><th>Process</th><th>Message</th></tr>' +
    CAST((
        SELECT
            td = CONVERT(VARCHAR(19), LogDate, 120), '',
            td = ProcessInfo, '',
            td = [Text], ''
        FROM #Warnings
        ORDER BY LogDate DESC
        FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX)) + N'</table>';
END

IF NOT EXISTS (SELECT 1 FROM #Errors) AND NOT EXISTS (SELECT 1 FROM #Warnings)
BEGIN
    SET @html += N'<p>No errors or warnings found in the last 24 hours.</p>';
END

SET @html += N'</body></html>';

-- Email if anything important exists, or send always by removing the WHERE clause
IF EXISTS (SELECT 1 FROM #Errors) OR EXISTS (SELECT 1 FROM #Warnings)
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'YourDatabaseMailProfile',
        @recipients   = 'ops@example.com',
        @subject      = 'SQL Server Error Log Summary - last 24 hours',
        @body         = @html,
        @body_format  = 'HTML';
END

Scheduling guidance

• Create a SQL Agent job that runs the script every morning at 7:00 AM.
• For mission critical systems, add a second schedule to run every 30 minutes and email only when errors or warnings exist.
• Keep a daily ticket or ops note with a short summary and links to any deeper investigations.

Final tips

• Pair this with DBCC CHECKDB alerts and backup verification. Error logs often reference those.
• If you see recurring entries, fix the root cause instead of muting the noise.
• If you see severity 20 or higher, or any assertion, collect diagnostics and open a formal incident.

See – Part 2 — Automate & Prioritize the SQL Server Error Log