As a Part of my routine DBA activity, I have to go through sp_readerrorlog to locate errors if any. In our production environment we have have Both Failed and Successful logins auditing enabled. Hence if i run sp_readerrorlog without any filters it would return me huge no of rows and go through each and every row is a time consuming and repetitive activity.
Here i would like to share how we can quickly go through Error Log with few filters.
sp_readerrorlog takes 4 parameters to narrow down our search.
- @p1 – Log file we want to read, 0 = current error log, 1 = Archive #1, 2 = Archive #2 etc..
- @p2 – 1 or null = SQL Server error log, 2 = SQL Agent error log.
- @p3 – string which we want to search.
- @p3 – string to further refine our search.
I basically filter one day error log as below.
EXEC sp_readerrorlog 0 , null , '2014-04-13 10:00' , '2014-04-14 10:00'
You can also refine your search just to check database backups like below.
EXEC sp_readerrorlog 0 , 1 , 'Database Mirroring' , 'error'
As sp_readerrorlog is undocumented stored procedure to review error log, you will not find any official references in Books online.
Keep your current error log under control just by recycling it, so that you can quickly retrieve its output, refer sp_cycle_errorlog.
Hope this helps…!