The undocumented nature of sp_readerrorlog in SQL Server 2000
Last updated: December 1st '01 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New

NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email with 'subscribe' in the subject line
See also:    
  Database programming guidelines, conventions SQL Server DBA and developer interview questions
  Evaluation of federated database servers SQL Server FAQ on programming, designing, administration, tools, replication
  SQL Server security best practices SQL Server administration best practices

Well, we all know that sp_readerrorlog can be used to read the SQL Server error logs from within T-SQL. If you call sp_readerrorlog without any parameters, it will display the current error log. You can go back to previous error logs, by specifying a number as the first parameter. So far so good.

One fine evening, I noticed a
post in the Microsoft SQL Server newsgroups, asking what the second, third and fourth parameters are for in sp_readerrorlog? Using sp_helptext, you can see that sp_readerrorlog accepts four parameters. But there's no information on the last three parameters. Well, that question went unanswered in the newsgroup. Not exactly, but a Microsoft technical support engineer answered saying, "It is undocumented". That response kind of made me curious and I started experimenting with sp_readerrorlog by passing it various kinds of parameters, analyzing the output, adjusting the parameters. Basically, a trial and error method :-) That wasn't difficult and exactly after experimenting for half an hour, I figured out the "Undocumented nature" of sp_readerrorlog. 

I concluded that sp_readerrorlog can be used to read any file on the SQL Server's hard disk, not necessarily the error logs, and that you can even do a keyword search on files. Cool feature, isn't it? Here's what I found:

You can make sp_readerrorlog read other files by specifying 

  • a non-zero first parameter
  • a valid file name as the second parameter
  • a valid line number, within the file as third parameter
  • a search string to find in that line, as fourth parameter

Based on the above conclusions, here are some examples of the undocumented behavior:

To read a file other than error log:

EXEC sp_readerrorlog 1, 'C:\Test.txt'

For the above example to work, the first parameter should be a valid number other than 0 and between 1 and 99.  Also,  the second parameter should be a valid file with full path.
To read a specific line from the file:

EXEC sp_readerrorlog 1, 'C:\Test.txt', 4

The above example reads the fourth line from the specified file. You must specify a valid line number for the third parameter or else nothing will be returned.
To read a specific line only if it contains a specified keyword:

EXEC sp_readerrorlog 1, 'C:\Test.txt', 4, 'Vyas'

The above example reads line four from the file 'C:\Test.txt', only if the fourth line contains the word 'Vyas'. I don't think, this fourth parameter accepts wildcard characters.

I verified this behavior on SQL Server 2000 with SP1.I could not reproduce it on SQL Server 7.0. Just a note: You should be a sysadmin or securityadmin to be able to execute sp_readerrorlog.

I just thought I will share this interesting finding with all of you, but I suggest you to stay away from building applications around this functionality. sp_readerrorlog may not work the same way in the next service pack or release of SQL Server 2000.

Disclaimer and terms of use
Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.