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
| Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line |
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.