Read SQL Server Error logs effectively

As a DBA, how often we check SQL error logs. I would say multiple times in a single day. Most important place for troubleshooting issues is SQL Server error log. But how many of us really know how to check and read the error logs effectively.

Well, this blog will solve your problem. We will start with various options that we have to read the error logs, and, in the end, I would share an example of small dynamic TSQL script that you can use to filter the error log.

The simplest way of checking the SQL error log is to use GUI, especially for the SQL server on a windows server. We can go to SSMS, connect the SQL instance and in the object explorer, under Management, we can see the SQL Server logs.

The current log is at the top followed by older logs with date and time. PFB screenshot:

You can open any of the log and see the log records. There is a filter option at the top which can be used to filter the log records as per User, Computer, Start Date, End Date etc. We can then apply filter and see the data required as per our need.

Second option is to read logs from the file in the directory where it is saved. It is a text file, location of which can be fetched using below command:

SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log file location';

Third option that we have to read SQL error logs is to use TSQL. Within, TSQL, again we have one stored procedure called sp_readerrorlog and one extended stored procedure called xp_readerrorlog. Both are almost same, and one can use any of them as per their liking.

Both these allow multiple parameters that can be used as per the requirement.

xp_readerrorlog @ArchiveID, @LogType, @FilterText1, @FilterText2, @FirstEntry, @LastEntry, @SortOrder

Below chart shows the meaning and use of each parameter:

Case scenario 1 – check the port number on which SQL is listening on:

xp_readerrorlog 0,1, N'listening', N'ipv4'

Case Scenario 2 – Check error logs for last four hours in a descending order:

xp_readerrorlog 0,1, NULL, NULL, '2022-11-29 13:00', '2022-11-29 17:00', N'Desc'

Best part is the ability to use dynamic TSQL query to read data from error logs. Consider a scenario that you want to filter the error log based on some distinct values, under some dynamic time range. Or you want to save certain portion of error log in a table for further processing in a different code. Below is one of the ways of doing this:

DECLARE @starttime datetime
DECLARE @endtime datetime
DECLARE @text NVARCHAR(max)

select @starttime = DATEADD(MINUTE, -60, GETDATE())
set @endtime = getdate()	

create table #errorlogdata(
[LogDate] datetime,
[Processinfo] varchar(20),
[textmessage] nvarchar(max))

Insert into #errorlogdata ([LogDate], [Processinfo], [textmessage]) exec xp_readerrorlog 0,1, N'The log for database', NULL, @starttime, @endtime, N'ASC'

Select DISTINCT textmessage from #errorlogdata

We can create a permanent table to store the logs for future references. We can add this kind of code in a SQL job if required.

I hope this blog must have provided you decent hacks to handle SQL error logs. Do share your inputs and scenario where you find these helpful.

Till then, Keep Learning, Keep Sharing..!