How to Read SQL Server Error Log Using sp_readerrorlog

I had an incident at work where I had to analyze the SQL Server error log. I was looking for a specific piece of information and so I thought I could just open the error log in notepad and find what I was looking for with the good old “CTRL+F.” That just took longer due to the confusing format. Below is a better and faster way.

The script below uses the sp_readerrorlog undocumented store procedure. You can replace the two parameters with whatever search string you’re looking for.

--Read error log stored procedure

--First Param: 0 = current, 1 = Archive #1, 2 = Archive #2, etc.
--Second Param: 1 or NULL = error log, 2 = SQL Agent log
--Third Param: String one you want to search for
--Fourth Param: String two you wan to search for to further refine results


--Below proc will search current error log for string 'recovery' and 'option'
exec sp_readerrorlog 0, 1, 'recovery', 'option'