SQL Tip-5 | Lightning Fast way to find errors in SQL Server Error Logs

In my first SQL Tip Don’t forget to recycle your SQLServer ErrorLog I listed some common problems you may face if you are not recycling your errorlogs timely. However, I do understand the criticality and importance of Error Log that’s the reason I also suggested you to increase the retention of your Error Logs in another tip Do you know you can increase number of archived Errorlogs.

But even after recycling your error logs frequently you may end up with such a large Error Log that it becomes tiresome finding errors or other text. Specially if you have many DBs and Frequency of Transaction Log backup is very high. In this tip I’ll talk about a method which can certainly help you find any error or text out of your errorlog without forcing you to read the complete errorlog.

let’s find the needle in a Haystack

SQL Server provides a Stored Procedure to read Error Logs; sp_readerrorlog.

By executing just the stored procedure without any parameter, it will give you the current SQL Server ErrorLog as an output.

Let’s say you want to read the previous error log, all you need to do is provide the first parameter i.e., Index number of Error Log. So if you want to read the Previous errorlog you can provide 1 as an input parameter on first position. <we are passing 1 for the previous errorlog because we are providing index number which is supplied by SQL Engine in reverse order starting from 0>

sp_readerrorlog 1

The Second positional input parameter is about log type:
a. 1 = SQL Server ErrorLog (Default Value)
b. 2 = SQL Agent ErrorLog

so if you ever want to read SQL Agent ErrorLog all you need to do is provide the second input parameter as 2.

Now the third and fourth parameters are search strings. If you want to search a text let’s say you want to know what all errors have been encountered since last rollover of Error Log;

sp_readerrorlog 1,1,’error’

This query will find your errorlog for the word “error” and return all rows having this word. You can further filter the rows by giving fourth parameter. If the fourth parameter is also specified this stored procedure will search the rows having both the values and return the output.

sp_readerrorlog 3,1, ‘error’, ‘18745’

You can specify any type of value in third\fourth parameter it could be alphanumeric or just numeric also.

Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips in your Inbox
Follow @SQLDB