How To Read The SQL Server Error Log

Reading the SQL Server Error Log is important when troubleshooting many issues for SQL Server. Some example issues would be errors related to Always On Availability Groups, long IO occurrence messages, and login failures.

Unfortunately, the SQL Server Error Log can be very full of information, making specific things hard to find, especially if you’re just visually scrolling the Error Log. Even if you’re recycling the Error Log each day and keeping 30 or more days of error log, on a busy system, the error log can still be quite full, even for a single day.

There are a couple of better ways to find the desired information.

Method 1: Use xp_ReadErrorLog to Read the SQL Server Error Log

This extended stored procedure takes the below parameters

LogNumber: The current log number is zero.

LogType: 1 equals the SQL Server Error Log, 2 relates to the SQL Agent Log

SearchItem1: This is text that you want to look for

SearchItem2: This is an additional search term to look for

StartDate:

EndDate:

SortOrder:

For example, the below will find any entries related to the Query Store feature.

EXEC xp_ReadErrorLog 0,1, N'Query Store'

The below will look for CHECKDB related entries, with no additional search string. It will return results that occurred between June 10th and June 11th and present them in descending order.

EXEC xp_ReadErrorLog 0,1, N'CHECKDB', NULL, '2022-06-10', '2022-06-11', N'DESC'

Method 2: Use PowerShell CMDLet Get-DbaErrorLog

If you’re managing a SQL Server and not using PowerShell, you are missing out on learning valuable skills that make work easier and faster.

For one thing, PowerShell can easily output results to a text file, csv or even an Excel format. This allows you to examine results later, outside the PowerShell window or provide those results to someone else later.

Look at DBATools.io to get the latest version of a powerful module that will help you work with SQL Server faster and remotely.

Get-DbaErrorLog is mostly a wrapper for xp_ReadErrorLog but requires you to enter the name of the SQL Instance you want to connect to, and this is part of what enables you to look at SQL Server Error Logs remotely.

There is an -After parameter that returns data after a certain date in the log and -Before returns data before a certain date in the log.

Here are couple of examples:

Get-DbaErrorLog -SqlInstance SQLServer01 -LogNumber 0 -Text "Query Store"




Get-DbaErrorLog -SqlInstance SQLServer01 -LogNumber 0 -Text "CHECKDB" -After '2022-06-10'

Next Steps To Take

1. Look at my blog series here, here, and here to get started with PowerShell.
2. If you have questions or comments, leave me a comment on this post, send me an email at [email protected], message me on Twitter or DM me on LinkedIn.