Today I was trying to find out if the differential backup for one of the biggest databases was successful. In my client’s environment all of the maintenance\backup jobs are scheduled through a third party tool. Since I didn’t remember the backup location and the job output file by heart, I planned to search the SQL Server Error Log for “Database differential changes were backed up….” message.
I usually use sp_readerrorlog stored procedure to read the error log. But in all the Production Instances that my team supports we have Both Failed and Successful logins auditing enabled. Hence the error log had a whopping 25562 lines for one day! Executing sp_readerrorlog display all the entries in the current error log. Searching for a particular message by means of Ctrl + F would be simply waste of time. Then I remembered that 4 parameters can be passed to sp_readerrorlog.
sp_readerrorlog [0,1,...], [1,2], ['text1'], ['text2']
The first parameter corresponds to the extension of the Error Log file. For Errorlog it will be 0, for Errorlog.1 it will be 1 and so on.
The second parameter allows only two values to be passed 1 or 2. The value 1 will search the SQL Server Error Log and the value 2 will search the SQL Agent Log.
Any value passed on to the third and fourth parameters will be searched in the log. If these parameters are not specified, the entire log will be printed.
To meet my requirements I executed sp_readerrorlog with the following parameters.
sp_readerrorlog 0,1,'Database Differential','MyDBName'
This will search for Database Differential and MyDBName values in the current SQL Server Error log. This query gave me the desired output quickly displaying only the relevant information.