In this article, we are going to explore xp_readerrorlog for reading SQL Server error logs using T-SQL command.

Reading SQL Server error log using T-SQL:

xp_readerrorlog

xp_readerrorlog Parameters Value Details:

xp_readerrorlog <paramter#1>,<paramter#2>,<paramter#3>,<paramter#4>,<paramter#5>,<paramter#6>,<paramter#7>

Parameter#1:Value of error log file that we want to read:
0 = current,
1 = Archive #1,
2 = Archive #2,
3 = Archive #3,
4 = Archive #4 etc…
Parameter#2: Error Log File Type:
1 or NULL = SQL Server error log
2 = SQL Server Agent log
Parameter#3:
First search string
Parameter#4:
Second search string to further refine the results

Parameter#5:
Third search string to specify 'from date'
Parameter#6:
fourth search string to specify 'to date'
Parameter#7:
fifth search string to specify 'asc'(ascending) or 'desc'(descending) for sorting.

Examples:

• Reading current SQL Server Error Log
  EXEC xp_readerrorlog 0,1

• Reading current SQL Server Agent Error Log
  EXEC xp_readerrorlog 0,2

• Reading Previous(Archive#1) SQL Server Error Log
  EXEC xp_readerrorlog 1,1

• Reading Previous SQL Server Agent Error Log
  EXEC xp_readerrorlog 1,2

• Reading current SQL Server Error Log with search string 'Error'
  EXEC xp_readerrorlog 0, 1, "Error"

Note: search string should be in double quotes, else we might see error as below.

• Reading current SQL Server Error Log with search string 'Failed'
  EXEC xp_readerrorlog 0, 1, 'Failed

• Reading current SQL Server error log with string 'Failed' and 'Login'
  EXEC xp_readerrorlog 0, 1, "Failed", "Login"

• Reading current SQL Server error log with string 'Failed' and 'Login' from 17 Sept, 2022
  EXEC xp_readerrorlog 0, 1, "Failed", "Login", "20220917" OR EXEC xp_readerrorlog 0, 1, "Login", "failed","2022-09-17"

• Reading current SQL Server error log with string 'Failed' and 'Login' between 16 Sept, 2022 and 17 Sept, 2022
  EXEC xp_readerrorlog 0, 1, "Failed", "Login", "20220916", "20220917"

• Reading current SQL Server error log with string 'Login' and 'failed' between 17 Sept, 2022 and 18 Sept, 2022 in descending order.
  EXEC xp_readerrorlog 0, 1, "Login", "failed", "2022-09-17", "2022-09-18", "desc"

How to check SQL Server error log physical location:

SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log location';

How to check SQL Server error log and its size:

EXEC sys.sp_enumerrorlogs;