Often times I keep checking the error log to make sure if everything is running fine. Here is an undocumented stored procedure that really helps simplify our task of searching the error log.
Exec XP_ReadErrorLog GO
This proc has 4 parameters:
- Parameter 1 (int), is the number of the log file you want to read, default is “0” for current log.
- Parameter 2 (int), value of 1 reads SQL error logs, value of 2 reads SQL Server Agent logs, default is 1.
- Parameter 3 varchar (255), is a search string for the log entry, with a default value of NULL.
- Parameter 4 varchar (255), is another search string for the log entry, with a default value of NULL.
Example searchings:
Exec XP_ReadErrorLog 0 Exec XP_ReadErrorLog 0, 1 Exec XP_ReadErrorLog 0, 1, 'deadlock' Exec XP_ReadErrorLog 0, 1, 'deadlock', 'MDW'
Update 01/21/2014:
If you are using 2012 SQL Servers, the above might not work, if so please try below
Exec XP_ReadErrorLog 0, 1, "deadlock" (OR) Exec Master..SP_ReadErrorLog 0, 1, 'deadlock'
Hareesh G
Jayapal Vajrala
Suresh Raavi
DataGinger.com 