Using xp_ReadErrorLog in SQL Server 2005

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.

Parameters revealed
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.

xp_ReadErrorLog


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.

xp_ReadErrorLog 0,2


LogDate ErrorLevel Text
2008-03-04 12:11:10.000 3 [393] Waiting for SQL Server to recover databases...
2008-03-04 12:11:14.000 3 [100] Microsoft SQLServerAgent version 9.00.3042.00 (x86 unicode retail build) ..
2008-03-04 12:11:14.000 3 [101] SQL Server xxxxxxxx version 9.00.3159 (0 connection limit)
2008-03-04 12:11:14.000 3 [102] 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: ]
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...

Parameters defined
* 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.

Background
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.