Using xp_ReadErrorLog in SQL Server 2005

By Guest Authors on 12 May 2008 | 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.

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

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