Searching the SQL Server logs using SQL

Category : Tips User Rating : 3.5 Stars      Views : 3 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.

SQL Server logs can be searched using SQL as an alternative to using the Log File Viewer in SQL Server Management Studio. This method uses an undocumented, but widely used, system stored procedure called xp_readerrorlog. This can be a lot quicker than loading the error log into the Log File Viewer.

To search for a string in the current SQL Server log :
exec xp_readerrorlog 0    -- 0=current SQL Server log
,1                        -- 1=SQL Server log, 2=SQL Agent log
,'failed'                 -- Search string
,''                       -- 2nd search string
This command above will search for all occurrences of the string ‘failed’ in the current SQL Server log.

The first parameter specifies which SQL Server log to search, 0 will always be the current one. The second parameter specifies whether to search the SQL Server log or the SQL Agent log. Finally the third and fourth parameters specify the string(s) to be searched for. If both parameters are specified then rows where both strings exist are returned.

To see which logs are available and what the range of dates contained in the logs are you can use the following command :
xp_enumerrorlogs 1
The parameter can be set to 0 or 1 to specify the SQL Server or SQL Agent logs, respectively.

I find that the first four parameters of xp_readerrorlog are sufficient for most purposes, but there are three further parameters which enable you to specify a range of dates to search, and whether the results should be sorted in date order or reverse date order.
exec xp_readerrorlog -- 0= current SQL Server log
,1                      -- 1=SQL Server log, 2=SQL Agent log
,''                     -- Search string
,'failed'               -- 2nd search string
,'24 Jan 2010 09:00'    -- Start time
,'24 Jan 2010 17:00'    -- End time
,'DESC'                 -- ordering ASC or DESC
The above will search the all records in the current sql server log between 24 Jan 2010 9am and 5pm for the string ‘failed’ and return the rows in reverse order (i.e. most recent first).
Link back to this article : the SQL Server logs using SQL.aspx




Post a comment   No login required !

Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered