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