ReadErrorLog
To read error logs in SQL Server using T-SQL you can use extended stored procedure xp_ReadErrorLog to read SQL Server and SQL Server Agent error logs. xp_ReadErrorLog has seven parameters that can be used to filter error logs.
Syntax for xp_ReadErrorLog:
EXEC xp_ReadErrorLog <LogNumber>, <LogType>,
<SearchTerm1>, <SearchTerm2>,
<StartDate>, <EndDate>, <SortOrder>
OR
EXEC sp_ReadErrorLog <LogNumber>, <LogType>,
<SearchTerm1>, <SearchTerm2>,
<StartDate>, <EndDate>, <SortOrder>
The parameter values can be as follows:
| Parameter | Values |
| Log number 0, 1, 2 … For example 0 returns current log. 2 returns logs from ERRORLOG.2 | |
| 1 – Reads SQL Server error logs, 2 – Reads SQL Server Agent error logs | |
| Search Term for Text Column | |
| Search Term for Text Column * When both search terms are specified, it only returns lines containing both terms | |
| Start reading logs from specified date | |
| Reads logs till this date | |
| ASC – Ascending or DESC – Descending |
You can use the stored procedure as:
EXEC xp_ReadErrorLog
– Reads current SQL Server error log
Below are some more examples of xp_ReadErrorLog:
EXEC xp_ReadErrorLog 1
– Reads SQL Server error log from ERRORLOG.1 file
EXEC xp_ReadErrorLog 0, 1
– Reads current SQL Server error log
EXEC xp_ReadErrorLog 0, 2
– Reads current SQL Server Agent error log
EXEC xp_ReadErrorLog 0, 1, 'Failed'
– Reads current SQL Server error log with text 'Failed'
EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login'
– Reads current SQL Server error log with text ‘Failed’ AND 'Login'
EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20121101', NULL
– Reads current SQL Server error log with text ‘Failed’ AND ‘Login’ from 01-Nov-2012
EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20121101', '20121130'
– Reads current SQL Server error log with text ‘Failed’ AND ‘Login’ between 01-Nov-2012 and 30-Nov-2012
EXEC xp_ReadErrorLog 0, 1, NULL, NULL, '20121101', '20121130'
– Reads current SQL Server error between 01-Nov-2012 and 30-Nov-2012
EXEC xp_ReadErrorLog 0, 1, NULL, NULL, '20121101', '20121130', 'DESC'
– Reads current SQL Server error log between 01-Nov-2012 and 30-Nov-2012 and sorts in descending order
