SQL Server keeps a configurable number of error log files as text files on disk. So you can’t simply use T-SQL directly to read those text files. Instead you can use the system stored procedure xp_readerrorlog.
When the error log files get large in SQL Server, opening them in Management Studio may be slow. Also, since the log files aren’t kept in tables but in text files, it’s not that simple to access the information using T-SQL. Using the system stored procedure xp_readerrorlog is a lot faster than the interface in SSMS, and can be used to view the result in a table format.
xp_readerrorlog
xp_readerrorlog can be run without parameters to see the complete current error log:
EXEC xp_readerrorlog
The columns are the same as can be seen from SSMS:
However, there are a number of useful parameters that can be used to control the result from xp_readerrorlog:
Parameter 1 – Is used to get a specified error log file. SQL Server keeps a number of historical error log files so use this parameter if you want to read an older error log file. The default is 0, which is the current error log file.
Parameter 2 – 1 = SQL Server error log (default), 2 = SQL Agent log
Parameter 3 – String search parameter to filter the results with a search string. Use unicode, that is N’search string’.
Parameter 4 – Additional search parameter.
Parameter 5 – To filter out rows older than this datetime value.
Parameter 6 – To filter out rows newer than this datetime value.
Parameter 7 – Sort order for the result. Either N’ASC’ or N’DESC’.
Below is a sample using xp_readerrorlog with the full set of parameters:
EXEC xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'ASC'
