Differences Between xp_readerrolog and sp_readerrorlog: #SQLNewBlogger

I was creating a question on sp_readerrorlog and realized that this procedure is different from the one it wraps: xp_readerrorlog. This post digs into a few differences.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The New System Stored Procedure

For most of my SQL Server career, it’s been a habit to use xp_readerrorlog to query the log. This was code I learned a lot time ago, and it’s worked in every version of SQL Server. Despite being undocumented, as you can see below:

xp_readerrorlog has been written up by a few people at SQL Server Central, including Nagaraj Venkatesan and Ken Fisher, as well as in some forum posts (one, two). However, it’s not well documented. I usually end up Googling for the parameters as I need them.

Until now.

Here are a few differences I’m documenting, so I will hopefully remember them.

Note: I got a few different results from AI, which were at best incomplete, and sometimes wrong.

sp_readerrorlog has different permissions. This proc works with anyone that has VIEW SERVER STATE, which is sysadmin, serveradmin, and security admin.  Or with the permission granted.

xp_readerrlog has extra parameters. Both of them have these parameters:

1. error log number (0 based)
2. error log type, 1 – SQL Server, 2 Agent
3. search value – needs to be NVARCHAR
4. second search value, NVARCHAR as well

xp_errorlog adds 5 and 6, and 7.

1. start time (datetime)
2. end time (datetime)
3. sort order (ASC,DESC)

Those are the main differences I see, and if you want to filter or sort, you need the extended stored procedure, not the wrapper.

I also learned to be careful of AI, as some of the data wasn’t correct from Google or Claude, so I need to verify what I get back and test how things work.

SQL New Blogger

I was investigating something and noticed a difference. This post was about 15 minutes to write, along with some testing of the parameters to verify what worked and what didn’t. That testing will make future posts quicker, as I’ll reuse some code.

This is a quick example of showing some knowledge, and including the warnings about AI. You can write this post and give someone confidence you’re a good choice for the next person to manage their database servers.