Skip to content

More about SQL Server Error log and SQL Server Agent Error Log

Talking about SQL Server and SQL Server agent error logs. Here are few interesting things that we can do from query analyzer in order to read and analyze the SQL Server and agent error logs.

XP_READERRORLOG

Syntax: xp_ReadErrorLog a,b,c,d

a -> default is 0. It accepts only integers. 0 mean the current error log, 1 means 1st archive and so on.

b -> default is 1. Accepts value equals 1 for SQL Server error log and 2 for SQL Agent error log.

c -> accepts varchar chanracter upto 255 characters. Default is Null.

d -> accepts varchar chanracter upto 255 characters. Default is Null.

1) To read the current SQL Server error log i.e. ERRORLOG file

xp_readerrorlog
or
xp_readerrorlog 0
or
xp_readerrorlog 0,1

2) TO read SQL Server error log archive 1 i.e. ERRORLOG.1 file

xp_readerrorlog 1
or
xp_readerrorlog 1,1

3) To read the current SQL Server Agent error log i.e. SQLAGENT.OUT file

xp_readerrorlog 0,2

4) To read SQL Server error log archive 1 i.e. SQLAGENT.1 file

xp_readerrorlog 1,2

5) To search for any specific text in current SQL Server error log

xp_ReadErrorLog 0, 1, 'Failed'

6) To search for entries in current SQL Server error log which contain both ‘Failed’ and ‘Login’ in a row.

xp_ReadErrorLog 0, 1, 'Failed'.'Login'

XP_ENUMERRORLOGS

Syntax: xp_enumerrorlogs a

a -> default is 1. Accepts value equals 1 for SQL Server error log and 2 for SQL Agent error log.

1) List all the avilable SQL Server error log archives, there last modified date and size.

xp_enumerrorlogs
or
xp_enumerrorlogs 1

2) List all the avilable SQL Server Agent error log archives, there last modified date and size.

xp_enumerrorlogs 2

To Recycle Current SQL Server Error Log

use master
go
exec sp_cycle_errorlog
go

To Recycle Current SQL Server Agent Error Log

use msdb
go
exec sp_cycle_agent_errorlog
go