SQLSERVERLEARNER

Read SQL Server Error Log Using TSQL Query

xp_readerrorlog procedure can be used to read error logs on SQL Server.

To read error logs you can use the following query:

[sql]
EXEC sys.xp_readerrorlog
[/sql]

By default this procedure reads the Current SQL Server error log.

In order to read the archive logs give the Archive log number as a parameter.

[sql]
EXEC sys.xp_readerrorlog 1
[/sql]

The above TSQL Query reads the first archive log.

To read the SQL Server agent error log, pass the second parameter value as 2

[sql]
EXEC sys.xp_readerrorlog 0,2
[/sql]

The above TSQL Statement reads the current error log for sql server agent.

The xp_readerrorlog procedure also has the ability to search for a string in error logs.
Searching error logs for a string can be achieved using the third parameter.

[sql]
EXEC sys.xp_readerrorlog 0,1,’error’
[/sql]

This TSQL query searches the current sql server error log for the string ‘error’ and returns all the rows that coontain the string error.

It is also possible to search for two string in the error log. The second string is the fourth parameter for the procedure xp_readerrorlog.

[sql]
EXEC sys.xp_readerrorlog 0,1,’error’,’17836′
[/sql]

The above TSQL Query searches for rows that contain both the srtings ‘error’ and ‘17836’ in the current sql server error log.

NOTE:
If the first parameter value exceeds the number of archive error logs present then the procedure gives the error.

[sql]
EXEC sys.xp_readerrorlog 50
[/sql]

Whey you execute the above query and if the 50th archive error log is not present in the system then you will get the below error:

Msg 22004, Level 16, State 1, Line 0
xp_readerrorlog() returned error 2, ‘The system cannot find the file specified.’