SQL Server Error Log: Where to Find It, How to Read It and What to Watch For

The SQL Server error log is the first place an experienced DBA looks when something goes wrong, and one of the most underused tools when nothing seems to be wrong. It records every startup, every failed login, every I/O problem the engine detects and every severe error, often hours or days before users notice a symptom.

This guide covers where the error log lives, how to read and search it with SSMS and T-SQL, which messages deserve immediate attention, how to keep it at a manageable size, and how to turn it from something you read after an outage into an early-warning system.

What Is the SQL Server Error Log?

The SQL Server error log is a plain-text file that the Database Engine writes to while the instance runs. Despite the name, it contains far more than errors. A typical log includes:

• Startup information: SQL Server version and edition, build number, server collation, memory and CPU detected, trace flags in effect, and the startup parameters used.
• Database startup and recovery: each database being started, how long crash recovery took, and how many transactions were rolled forward or back.
• Authentication failures: failed logins (error 18456) with the client IP address and a state code that reveals the real reason.
• Backup and restore events: every successful backup and restore by default, and every failure.
• Errors with severity 19 through 24, which Microsoft documents as always written to the error log, plus any message raised WITH LOG.
• Health warnings such as slow I/O, read retries, memory pressure and non-yielding schedulers.
• High availability events such as Always On availability group role changes and mirroring state changes.

Where Is the SQL Server Error Log Located?

The default location depends on the platform and the instance:

• Windows: <drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<InstanceName>\MSSQL\Log\ERRORLOG (for example MSSQL16.MSSQLSERVER for a default SQL Server 2022 instance).
• Linux: the errorlog file under /var/opt/mssql/log/.

The current log has no extension. Older logs are kept as ERRORLOG.1 (the most recent archive), ERRORLOG.2 and so on. Because the path can be changed during setup, the most reliable way to find it on a running instance is to ask SQL Server directly:

-- Full path of the current error log file
SELECT SERVERPROPERTY('ErrorLogFileName') AS current_error_log;

-- List the current log and all archives with their dates and sizes
EXEC sys.sp_enumerrorlogs;

When SQL Server will not start

If the service fails to start, you can’t query anything, but the error log is still written up to the point of failure, and it is usually the fastest way to find the cause. Open SQL Server Configuration Manager, open the properties of the SQL Server service, and on the Startup Parameters tab look at the -e parameter: that is the full path of the error log. Open the file in any text editor and read it from the bottom up. Typical startup blockers are a missing or locked master or tempdb file, a drive that didn’t come online, or a service account without permission on a data folder. The Windows Application event log contains the same SQL Server messages and is a useful second source.

How to Read the SQL Server Error Log

Option 1: SSMS Log File Viewer

In SQL Server Management Studio, expand the instance in Object Explorer, then Management → SQL Server Logs. Double-click Current or any archive to open the Log File Viewer. From there you can tick several logs at once (including SQL Server Agent and Windows logs) to see them in one timeline, and use Filter to limit by date range, source or message text. This is convenient for a quick look, but it gets slow on very large log files, which is one more reason to keep the log size under control (see below).

Option 2: sp_readerrorlog in T-SQL

For repeatable searches and scripts, use the documented system procedure sp_readerrorlog. It takes up to four parameters:

• @p1: which log to read. 0 is the current log, 1 is ERRORLOG.1, and so on.
• @p2: which product. 1 (or NULL) for the SQL Server error log, 2 for the SQL Server Agent log.
• @p3: a search string.
• @p4: a second search string, which further narrows the results of the first.

-- Read the current SQL Server error log
EXEC sys.sp_readerrorlog 0, 1;

-- Failed logins in the current log
EXEC sys.sp_readerrorlog 0, 1, N'Login failed';

-- Lines that contain both "Error" and "Severity" in the previous log
EXEC sys.sp_readerrorlog 1, 1, N'Error', N'Severity';

-- The previous SQL Server Agent log
EXEC sys.sp_readerrorlog 1, 2;

You will also see xp_readerrorlog in many scripts. It is the extended procedure behind sp_readerrorlog and additionally accepts a start time, an end time and a sort order. It is not officially documented by Microsoft, so it’s fine for ad-hoc troubleshooting but better avoided in production tooling that you expect to survive future versions.

Search every archived log at once

sp_readerrorlog reads one file at a time. When you are investigating an incident that may have started before the last restart, the script below reads all available SQL Server error logs into a table variable and returns the last seven days of entries that look like problems:

DECLARE @logs TABLE (ArchiveNo int, LogDate datetime, LogSizeBytes bigint);
DECLARE @entries TABLE (LogDate datetime, ProcessInfo nvarchar(100), LogText nvarchar(max));
DECLARE @i int = 0, @max int;

INSERT INTO @logs EXEC sys.sp_enumerrorlogs;
SELECT @max = MAX(ArchiveNo) FROM @logs;

WHILE @i <= @max
BEGIN
    INSERT INTO @entries EXEC sys.sp_readerrorlog @i, 1;
    SET @i += 1;
END;

SELECT LogDate, ProcessInfo, LogText
FROM @entries
WHERE LogDate >= DATEADD(DAY, -7, SYSDATETIME())
  AND (   LogText LIKE N'%Error:%'
       OR LogText LIKE N'%failed%'
       OR LogText LIKE N'%longer than 15 seconds%'
       OR LogText LIKE N'%paged out%'
       OR LogText LIKE N'%Non-yielding%')
ORDER BY LogDate DESC;

Remember that SQL Server usually writes an error as two consecutive lines: one with Error: <number>, Severity: <n>, State: <n> and one with the actual message. When a line matches, look at the entries with the same timestamp and SPID to get the full picture.

How to Interpret an Error Log Entry

Every row has three parts: a timestamp, a source (Server, Logon, Backup, or spidNN for the session that raised it) and the message. Error messages carry three numbers that are worth reading carefully:

• Error number: identifies the problem. Every number has its own page on Microsoft Learn (search for MSSQLSERVER_<number>).
• Severity: how serious it is (see the table below).
• State: where in the code the error was raised. For most errors this matters only to Microsoft Support, but for some, like login failure 18456, the state is the most useful part of the message.

Error Log Messages That Need Immediate Attention

A busy instance writes thousands of lines a day, and most of them are harmless. These are the messages we look for first when reviewing a SQL Server error log, because each one either is an outage or tends to precede one:

Decoding login failures (error 18456)

To avoid helping attackers, SQL Server tells the client only that the login failed. The real reason is in the error log as the state, for example Error: 18456, Severity: 14, State: 8. The states you will meet most often are:

• State 2 or 5: the login doesn’t exist. Often an application pointing at the wrong server after a migration.
• State 7: the login is disabled and the password is wrong.
• State 8: wrong password.
• State 11 or 12: the login is valid but server access failed, often a Windows user who only has access through a group or UAC.
• State 18: the password must be changed.
• State 38 or 46: the database requested in the connection string can’t be opened, because it’s misspelled, offline or being recovered.
• State 58: SQL authentication was attempted on a server configured for Windows authentication only.

By default SQL Server logs failed logins only. You can change this under Server Properties → Security → Login auditing (the change takes effect after a service restart). Logging successful logins as well is rarely a good idea on a busy server, because it floods the error log. If you need an audit trail of who connected, SQL Server Audit is the better tool.

What the Error Log Does Not Record

Many older articles claim the error log captures slow queries, deadlocks or schema changes. By default, it doesn’t, and looking for them there wastes valuable time during an incident:

• Slow queries are not logged. Use Query Store or Extended Events.
• Deadlocks are not logged by default. Trace flag 1222 writes deadlock details to the error log, but Microsoft advises against it on workload-intensive systems. The built-in system_health Extended Events session already captures deadlock graphs, and you can read them without changing anything.
• Object creation, changes and deletions are not logged. The default trace or SQL Server Audit records those.
• Successful logins are not logged unless you change the login auditing setting described above.

Managing Error Log Size and Retention

SQL Server starts a new error log every time the service starts. The current file becomes ERRORLOG.1, the previous .1 becomes .2, and so on. By default, SQL Server keeps the current log plus six archives, and the oldest is deleted when a new one is created.

That default causes two opposite problems in practice:

• Logs that grow huge: on a server that isn’t restarted for months, the current log can reach hundreds of megabytes or several gigabytes. SSMS then takes minutes to open it, exactly when you are in a hurry.
• History that disappears: if a server is restarted several times in a row during an incident or patching, six restarts are enough to wipe out the log that contained the original cause.

The fix is to cycle the log on a schedule and keep more archives.

Cycle the error log without a restart

sp_cycle_errorlog closes the current log and starts a new one, exactly as a restart would, but without any downtime. Schedule it as a weekly (or, on very busy servers, daily) SQL Server Agent job. The Agent log has its own equivalent:

-- Start a new SQL Server error log
EXEC sys.sp_cycle_errorlog;

-- Start a new SQL Server Agent log (SQLAGENT.OUT)
EXEC msdb.dbo.sp_cycle_agent_errorlog;

To set up the weekly cycle, create a small SQL Server Agent job that runs both procedures every Sunday at midnight. Change the schedule to suit your maintenance window:

USE msdb;
GO
-- Weekly job: cycle the SQL Server and SQL Server Agent logs every Sunday at 00:00
EXEC dbo.sp_add_job        @job_name = N'DBA - Cycle error logs';
EXEC dbo.sp_add_jobstep    @job_name = N'DBA - Cycle error logs',
                           @step_name = N'Cycle logs',
                           @subsystem = N'TSQL',
                           @database_name = N'master',
                           @command = N'EXEC sys.sp_cycle_errorlog; EXEC msdb.dbo.sp_cycle_agent_errorlog;';
EXEC dbo.sp_add_schedule   @schedule_name = N'Weekly - Sunday 00:00',
                           @freq_type = 8,              -- weekly
                           @freq_interval = 1,          -- Sunday
                           @freq_recurrence_factor = 1, -- every week
                           @active_start_time = 0;      -- 00:00:00
EXEC dbo.sp_attach_schedule @job_name = N'DBA - Cycle error logs',
                            @schedule_name = N'Weekly - Sunday 00:00';
EXEC dbo.sp_add_jobserver  @job_name = N'DBA - Cycle error logs',
                           @server_name = N'(local)';

Keep more archives and cap the file size

In SSMS, right-click Management → SQL Server Logs → Configure. Tick Limit the number of the error log files before they are recycled and set a higher maximum (up to 99). Recent SSMS versions also let you set a maximum size per file in KB, and 0 means unlimited. With a weekly cycle, 30 archives give you more than six months of history. The same settings can be scripted, which is useful when you standardize many instances:

-- Keep 30 archived error logs
EXEC master.dbo.xp_instance_regwrite
     N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'NumErrorLogs', REG_DWORD, 30;

-- Optional: start a new log when the current one reaches ~50 MB
EXEC master.dbo.xp_instance_regwrite
     N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'ErrorLogSizeInKb', REG_DWORD, 51200;

Reduce the noise

By default every successful backup writes a line to the error log. With log backups every 5–15 minutes across dozens of databases, those lines can make up most of the log and bury the messages that matter. Trace flag 3226 suppresses these success messages; failures are still logged. Add it as a startup parameter (-T3226) in SQL Server Configuration Manager so it survives restarts. First make sure no script or monitoring tool relies on those success lines, and remember that backup history is still recorded in msdb.

From Reading Logs to Getting Alerted

Reviewing the error log every morning is good practice, but critical errors shouldn’t wait for someone to read them. SQL Server Agent alerts can notify you the moment a severe error is logged. A sensible baseline is one alert per severity from 17 to 25, plus specific alerts for errors 823, 824 and 825. Error 825 is logged as an informational message, so a severity-based alert would never catch it. 823 and 824 would trigger the severity 24 alert anyway, but a dedicated alert makes the notification say exactly what happened.

-- Assumes Database Mail and an operator named 'DBA Team' already exist
DECLARE @sev int = 17, @name sysname;
WHILE @sev <= 25
BEGIN
    SET @name = N'Severity ' + CAST(@sev AS nvarchar(2));
    EXEC msdb.dbo.sp_add_alert
         @name = @name, @severity = @sev,
         @delay_between_responses = 60, @include_event_description_in = 1;
    EXEC msdb.dbo.sp_add_notification
         @alert_name = @name, @operator_name = N'DBA Team', @notification_method = 1;
    SET @sev += 1;
END;

DECLARE @err int;
DECLARE err_cur CURSOR LOCAL FAST_FORWARD FOR SELECT v FROM (VALUES (823), (824), (825)) AS e(v);
OPEN err_cur;
FETCH NEXT FROM err_cur INTO @err;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @name = N'Error ' + CAST(@err AS nvarchar(5));
    EXEC msdb.dbo.sp_add_alert
         @name = @name, @message_id = @err, @severity = 0,
         @delay_between_responses = 60, @include_event_description_in = 1;
    EXEC msdb.dbo.sp_add_notification
         @alert_name = @name, @operator_name = N'DBA Team', @notification_method = 1;
    FETCH NEXT FROM err_cur INTO @err;
END;
CLOSE err_cur; DEALLOCATE err_cur;

@delay_between_responses = 60 stops a burst of identical errors from flooding your inbox. Set up a fail-safe operator as well, so alerts are never silently dropped. For a fuller walkthrough, see our guide on the right way to set up SQL Server alerts.

A Practical Error Log Review Routine

Alerts catch the critical events. A short regular review catches the trends that alerts miss. A routine that works well on most estates:

1. Daily: scan the last 24 hours for errors with severity 17 and above, I/O warnings (825, 833), memory warnings (17890, 701) and backup failures (3041).
2. Daily: count failed logins by state and client IP. A sudden jump almost always means either a deployment with a wrong connection string or someone guessing passwords.
3. After every restart or failover: read the startup section. Confirm the build number, trace flags, memory detected and that every database finished recovery.
4. Weekly: cycle the error log and the Agent log, and confirm that archives older than your retention period are rotating out.
5. Monthly: look for repeating patterns, such as 833 warnings at the same hour every night, which usually line up with backup jobs, antivirus scans or storage snapshots.

This fits naturally into a broader SQL Server monitoring routine and is one of the first things we check in a SQL Server health check.

A Note on Azure SQL

Azure SQL Managed Instance exposes the error log much like SQL Server does, so sp_readerrorlog works there. Azure SQL Database (single databases and elastic pools) doesn’t give you access to an instance error log. There, the equivalent information comes from Azure Monitor diagnostic settings, Extended Events and the database’s resource and audit logs.

Frequently Asked Questions