The parameters of xp_readerrorlog

  • Article

 

I find that few people know xp_readerrorlog accepts parameters to help you mine information out of the SQL Error log (as well as the SQL Agent error log).  The SQL Error log has a wealth of information.   In this post, we’ll look at some of those parameters.

Running xp_readerrorlog without any parameters returns the current SQL Server error log (ERRORLOG).   The first parameter specifies which log to return and the default is “0”:

xp_readerrorlog 1

This statement will return the contents of ERRORLOG.1.

However, if you put a value for a log file extension that does not exist, like 9 for ERRORLOG.9, you get:

Msg 22004, Level 16, State 1, Line 0
xp_readerrorlog() returned error 2, 'The system cannot find the file specified.'

For the second parameter,  you specify which log to review.  The default is 1 for the SQL Server database engine, but you can specify a 2 for the SQL Server agent.  Anything larger than a 2 here will return an invalid parameter.

The 3rd and 4th parameters allow you to specify 2 search strings to search the log with.  So to find the server process id assigned to SQL Server from the current error log using xp_readerrorlog, execute the following:

xp_readerrorlog 0, 1, “server”, “process ID”

and you get:

image

 

- Jay