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 -- 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).