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