Sometimes the SQL Server ERRORLOG file can become very large. This happens typically when you have turned on auditing for successful and failed logins. Additionally if you don’t restart SQL Server very often, or don’t recycle the ERRORLOG these files can become quite large; so large that it becomes difficult to browse the file looking for a period of time, or error message string. When the error log file is large, using some text editor or the GUI interface within SQL Server Management Studio to browse through the different error log files can be difficult, downright frustrating, and sometime impossible.
To simplify the reading of the large error log file, and provide a little less frustrating process you can use the undocumented extended stored procedure called “xp_readerrorlog”. This extended stored procedure allows you to search those large error log files. This extended stored procedure can read both the SQL Server ERRORLOG and the SQL Agent log files. Since this extended stored procedure is undocumented there is no official documentation from Microsoft explaining how to use this stored procedure. But if you use your favorite internet search engine looking for information about xp_readerrorlog there are a number of posts that explain how the extended store procedure works, and the parameters you can use to search your large ERRORLOG file.
There are seven different parameters you can pass to xp_readerrorlog extended stored procedure:
Parm 1 = Identifies the error log file that you would like read. Set this parm to 0 if you’d like to read the current error log. Or you can set in to either 1, 2, 3, etc. to read one of the historical error log files.
Parm 2: Identifies which error log to search. 1, or null for ERRORLOG, or 2 for the SQL Agent log
Parm 3: The first string you want to search for in the error log file.
Parm 4: The second string you want to search for in the error log file.
Parm 5: The start time constraint on searching.
Parm 6: The end time constraint on searching.
Parm 7: Sort order of the output (asc, desc)
Let’s look at a couple of different common uses for this extended stored procedure.
First let’s assume you want to read the current ERRORLOG file and display all the log records between a start time and an end time. To accomplish this you run the xp_readerrorlog process using the following code:
--Find ERROR log record between 2016-09-20 14:34 and 2016-09 15:50 EXEC xp_readerrorlog 0, 1, null, null, '2016-09-20 14:34', '2016-09-20 15:00 ', 'asc'
Or, if you wanted to search for a specific string of text like ‘error’, then you would run this code:
--Find ERRORLOG records with the word ‘error’ in the message text in the ERRORLOG file EXEC xp_readerrorlog 0, 1, error, null, null, null, 'asc'
You can even search for two different character strings, like ‘error’ and ‘Program’ by running the following query:
--Find ERORRLOG records the word ‘error’ and ‘Program’ are in the message text in the ERRORLOG file EXEC xp_readerrorlog 0, 1, error, Program, null, null, 'asc'
