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.


Related Articles

Advanced SQL Server 2008 Extended Events with Examples (25 May 2009)

Introduction to SQL Server 2008 Extended Events (19 May 2009)

Monitoring SQL Server Agent with Powershell (24 March 2009)

SQL Server Version (29 January 2009)

Scheduling Jobs in SQL Server Express - Part 2 (1 December 2008)

Alerts for when Login Failures Strike (14 July 2008)

Moving the tempdb database (5 November 2007)

Centralized Asynchronous Auditing across Instances and Servers with Service Broker (20 August 2007)

Other Recent Forum Posts

As I gain experience and get older, I'm working much slower, but producing better quality, but (3h)

Master DB 2019 problem (17h)

Please help, I can't login remote to MS SQL 2019 without sysadmin role (1d)

SSMS Cannot Connect to Newly Installed Instance (2017) (1d)

SQL server 2019 alwayson problem (2d)

Finding Possible Duplicates (4d)

SQL Agent Service will not start - timeout error (5d)

Adding a SQL connection to Microsoft Visual Studio (5d)

- Advertisement -