Sql And Me

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>

The parameter values can be as follows:

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

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe