xp_readerrorlog/sp_readerrorlog in SQL Server

xp_readerrorlog/sp_readerrorlog

SQL Server support undocumented extended stored procedures (xp_readerrorlog) to read error logs, we can also see proc sp_readerrorlog, which ultimately uses procedure xp_readerrorlog in its definition

t-SQL code of sp_readerrorlog:



create proc sys.sp_readerrorlog(
@p1 int = 0,
@p2 int = NULL,
@p3 nvarchar(4000) = NULL,
@p4 nvarchar(4000) = NULL)
as
begin

if (not is_srvrolemember(N'securityadmin') = 1)
begin
raiserror(15003,-1,-1, N'securityadmin')
return (1)
end

if (@p2 is NULL)
exec sys.xp_readerrorlog @p1
else
exec sys.xp_readerrorlog @p1,@p2,@p3,@p4
end




When we execute xp_readerrorlog/sp_readerrorlog it returns sample result as below.




As per definition of sp_readerrorlog, procedure will accept four parameters (@p1, @p2, @p3, and @p4)
Where parameters requires values as below.
@p1 [Log Number] : 0 for current log, 1 for log achieve#1, 2 for log achieve#2 etc…
@p2 [Log Type] : 1  Read SQL server error log, 2  Read SQL server agent error log.
@p3 [Search String1]: first parameter value to search in field Text
@p4 [Search String4]: second parameter value to search in field Text

All above parameters also have default values as 0 or NULL.

Let us take below example for sp_readerrorlog
EXEC sys.sp_readerrorlog 0, 1 ,'failed','user'
According to above statement procedure will read errors from current SQL Server error Log.

In real, actual extended stored procedure is XP_readerrorlog, it accept three more parameters
StartTime,EndTime,SortOrder these three parameters satisfy column LogDate from procedure result

Example:
EXEC sys.xp_readerrorlog 0, 1 , N'failed',N'user','2015-05-25 15:13:02.410','2015-06-15 13:54:10.550','ASC';

According to above statement procedure will read errors from current SQL Server error Log and will have filter criteria on LogDate column as startDate and EndDate in Ascending order

Procedure with required sample parameters name are:

EXEC XP_readerrorlog [LogNumber],[LogType],[SearchStr1],[SearchStr2],[StartTime],[EndTime],[SortOrder]