Using xp_ReadErrorLog in SQL Server 2005
By Guest Authors
on 12 May 2008
| 8 Comments
| Tags: Administration
I would like to share some interesting parameters I found for the undocumented extended stored procedure xp_ReadErrorLog. In doing some testing with this extended stored procedure I found four very interesting parameters. Adding to some of the articles already on the web that discuss undocumented stored procedures, in this article I will explain my testing, use and some examples of the procedure.
While working on some system startup procedures that would be making use of the xp_ReadErrorLog extended
stored procedure, I came across some very interesting and useful parameters for the procedure. In testing I
discovered some of the hidden parameter options that are similar, but still different in the way the extended
stored procedure works from version SQL 2000 to SQL 2005. These are SQL 2005 only options. The parameter
data types and size were determined by investigating the undocumented stored procedure sp_ReadErrorLog that
uses the extended stored procedure.
Well, the interesting part starts now with the parameters. As in the previous versions parameter 1 reads the
error log number passed to it, where the default "0" reads the current log.
LogDate ProcessInfo Text
2008-03-04 12:11:01.450 Server Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86) Mar 23 2007 16:15:11
2008-03-04 12:11:01.500 Server (c) 2005 Microsoft Corporation.
2008-03-04 12:11:01.500 Server All rights reserved. 2008-03-04 12:11:01.500 Server Server process ID is 1284.
2008-03-04 12:11:01.500 Server Authentication mode is MIXED.
2008-03-04 12:11:01.510 Server Logging SQL Server messages in file 'D:\SRVAPPS\MSSQL.1\MSSQL\LOG\ERRORLOG'.
Now let's investigate Parameter (2). It turns out that a value of 1 (default) instructs the procedure to
read the SQL error log. By passing a value of 2, the SQL server Agent log is read. Yes, the Agent log!
So for example: xp_ReadErrorLog 0, 2 reads the current SQL server Agent log. Also note when using
parameter 2 with the extended stored procedure that the column heading returned also changes.
LogDate ErrorLevel Text
2008-03-04 12:11:10.000 3  Waiting for SQL Server to recover databases...
2008-03-04 12:11:14.000 3  Microsoft SQLServerAgent version 9.00.3042.00 (x86 unicode retail build) ..
2008-03-04 12:11:14.000 3  SQL Server xxxxxxxx version 9.00.3159 (0 connection limit)
2008-03-04 12:11:14.000 3  SQL Server ODBC driver version 9.00.3042 2008-03-04 12:11:01.450 Server
Now we know that we can read both SQL logs (error and agent) for any log file, so now let's look at
parameter (3). For those times when you need to find some value in the logs and have used the old
trick/process of reading the extended stored procedure into a table and then searching through the
table to find a value, we now have parameter (3). Parameter 3 is a search string that can be used
to return just the log entry rows that contain the value in the search string. And to make it even
better or to refine the search further, parameter 4 is also a search string.
An extra feature of this new version is that parameters 3 and 4 can be used in conjunction
with each other for searching SQL error log (parameter 2 = 1) or SQL Agent log (parameter 2=2).
So for example, xp_ReadErrorLog 0,1,'failed' will read the current SQL error log and return only
rows that contain "failed". For an example of using parameter (4) example,
xp_ReadErrorLog 0,1,'failed','login' will read current SQL error log returning only rows
that contain "failed" and "login" in the same row. This makes it quite easy for retrieving
those log entries for failed user logins from the SQL error logs, or maybe looking for those
failed Agent jobs. Or those occasional times when you need to quickly find the port SQL started
on or what machine the cluster is executing on.
xp_ReadErrorLog 0, 1, 'Failed', 'login'
LogDate ProcessInfo Text
2008-03-04 12:11:12.340 Logon Login failed for user 'Domain\xxxxxx'. [CLIENT: <local machine>]
2008-03-04 15:29:08.710 Logon Logon failed for login 'NT AUTHORITY\NETWORK SERVICE' due to trigger execution.
2008-03-04 15:29:08.710 spid54 The client was unable to reuse a session with SPID 54, which had been reset...
- Parameter 1 (int), is the number of the log file you want to read, default is "0" for current log.
The extended stored procedure xp_enumerrorlogs will come in handy in determining what SQL server error
logs or SQL server Agent logs exist and when they were created. NOTE: extended stored procedure
xp_enumerrorlogs parameter (1) works just like xp_ReadErrorLog parameter (2).
A value of 1 shows available SQL error logs and a value of 2 shows Agent logs.
The default value is 0.
- Parameter 2 (int), value of 1 reads SQL error logs, value of 2 reads SQL Server Agent logs, with a default value of 1.
- Parameter 3 varchar (255), is a search string for the log entry, with a default value of NULL.
- Parameter 4 varchar (255), is another search string for the log entry, with a default value of NULL.
An extended stored procedure is a dynamic link library that runs inside SQL server. It can execute
from Query Analyzer or SQL Server Management Studio (SSMS) for example. In most cases these extended
stored procedures can only be executed by users with sysadmin privileges. Also note as Microsoft has
always said about undocumented processes may change in future release. And this is an example as it
certainly has changed from previous version of SQL.
About the Author
Dan McClain is currently the Team Lead for the SQL Server DBA team at
Anheuser-Busch, Inc. in Saint Louis, Missouri. He has been working in IT for over 25 years and
with database for over 12. He is active in the local SQL Users Group.