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'