SQL Server Errorlog: Not Just for Errors!

SQL Server’s Error Logs often get overlooked, until something breaks. But if you know how to use them, they can tell you what went wrong before you’re knee-deep in outage mode.

Whether you're a sysadmin who inherited SQL or an MSP juggling multiple environments, understanding the ErrorLog is low effort, high return.

Let’s decode the basics.

What Is the SQL Server Error Log?

It’s not a crash dump. Its not even just errors. The errorlog is a plain text file that tracks:

•  Startup and shutdown events

• Login successes and failures (settable)

• Backup and restore activity

• Deadlocks (if enabled)

• Corruption errors

• Memory dump ingo

• Full data and log file warnings

• And more!

 It's like a flight recorder for SQL Server. If something bad happens, it probably shows up here.

How Many Logs Are There? And Where Are They?

By default, SQL Server keeps 7 error logs (Errorlog (current) through Errorlog.6). Each gets recycled when the SQL Server service restarts or the logs are manually cycled. They are located in the same folder as your SQL Server binaries (or wherever SQL was installed)

If you can’t find them see this short video: How to find the SQL Server Errorlog

Also, the location is the -e startup parameter in SQL Server Configuration Manager

 You can view them in SQL Server Management Studio under Management > SQL Server Logs or directly from the file system.

Changing the Retention Settings

Seven logs may not be enough if you cycle the service frequently. Here’s how to increase retention:

USE [master];
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 0
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 25
GO

If you don’t like registry edits, use SSMS: 

Management>>SQL Server Logs>>Right-click: Configure. Check the “Limit the number of error log...” box, change the “Maximum number of error log files” to your preference and click OK:

You can also cycle the log manually without restarting SQL Server:

EXEC sp_cycle_errorlog;

Do this regularly (e.g., weekly) via SQL Agent job so each log stays readable and relevant.

I like weekly cycling, 25 retention so I have 6 months of reasonably sized log files.

Finding the Signal in the Noise (Filtering)

The error log can be…wordy. Thankfully, SSMS lets you filter by multiple options:

For command-line lovers:

EXEC sp_readerrorlog 0, 1, 'Login failed';

This searches the current log for login failures.

Want to scan older logs? Change the first parameter:

EXEC sp_readerrorlog 3, 1, 'backup';  -- Searches the 4th oldest log

(xp_readerrolog exists, but is undocumented. Use sp_readerrorlog instead)

More Filtering/Cleanup:

If you can, log only failed Logins, not failed and successful. Your industry may require other setups.

Use trace flag 3226 to NOT log successful transaction log backups. Those are already being logged in the msdb tables and job history.

The Bottom Line: Set It and Watch It

Check retention: 7 logs may not be enough

• Automate log cycling so logs don’t get so big they are unreadable

• Use filters to quickly find useful info

• Some monitoring tools read the logs for you, but you should still know how

Get 2 Hours of Coaching from Me!

Over-Caffeinated DBA Coaching

Get 25% off by using coupon code: NEWSLETTER

(coupon expires June 30, 2025. This is not for support services)

SQL tidBITs:

Want to find out when SQL Server was last restarted?
That info is in the Error Log, but only if the log hasn’t been cycled since startup. Use this to check the logs:

EXEC xp_readerrorlog 1, 1, 'SQL Server is starting'; -- Searches Errorlog.1

Or get it straight from the system view, which is always reliable:

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Please share with someone that is not cycling their Errorlogs and they are filling the drive!