SQL Server – How to read the SQL Server Error log files using TSQL

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu
SQL Server - How to read the SQL Server log files using TSQL

SQL Server – How to read the SQL Server log files using T-SQL

There is undocumented system stored procedure sp_readerrorlog which allows us to read the SQL Server error log files directly using T-SQL.

This procedure has total 7 parameters as given below:

Parameter Values
First Parameter Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc.
Second Parameter Log file type : 1 – Reads SQL Server error logs,
2 – Reads SQL Server Agent error logs
Third Parameter Search string 1: String Value
Forth Parameter Search string 1: String Value
Fifth Parameter Start Date: Start Date reading logs from specified date
Sixth Parameter End Date: End Date reading logs from specified date
Seventh Parameter Sort Order : ASC – Ascending or DESC – Descending

 

Important Note: Without passing any parameters this SP will return the contents of the current error log.

Example:

--Return the Cuurent SQL Server Error Log
EXEC xp_ReadErrorLog 0,1 

--Return the Cuurent SQL Agent Error Log
EXEC xp_ReadErrorLog 0,2 

-- Reads current SQL Server error log with text 'sql'
EXEC xp_ReadErrorLog 0, 1, N'sql' 

-- Reads current SQL Server error log with text 'sql' and 'error'
EXEC xp_ReadErrorLog 0, 1, N'sql' , N'error' 

-- Reads current SQL Server error log with text 'sql' and 'error'
EXEC xp_ReadErrorLog 0, 1, N'sql' , N'error', N'20141027' 

-- Reads current SQL Server error log for the specfic date'
EXEC xp_ReadErrorLog 0, 1, Null, Null, N'20141027' 

-- Reads current SQL Server error log for the specfic date in Descending order
EXEC xp_ReadErrorLog 0, 1, Null, Null, N'20141027',Null, N'Desc'

Hope it helps !

Related Posts

, , ,