I would like to share some interesting parameters I found for the undocumented extended stored procedure xp_ReadErrorLog. In doing some testing with this extended stored procedure I found four very interesting parameters. Adding to some of the articles already on the web that discuss undocumented stored procedures, in this article I will explain my testing, use and some examples of the procedure.

Instance   20-Jun-2008 19-Jun-2008 18-Jun-2008
INST1         0           4           0
INST4         55          0           0

set nocount on;

create table #el90 (LogDate datetime, ProcessInfo varchar(20), log_text varchar(7000), log_date as convert(datetime, convert(varchar(20), LogDate, 106)))
create table #el80 (log_text varchar(255), cont_row bit, log_date as case when isdate(left(log_text, 10)) = 1 and cont_row = 0 then convert(datetime, left(log_text, 10)) else null end);
create table #el (log_text varchar(7000), log_date datetime null)

if @@version like '%9.00.%'
begin
	insert #el90 (LogDate, ProcessInfo, log_text) exec xp_readerrorlog;
	insert #el90 (LogDate, ProcessInfo, log_text) exec xp_readerrorlog 1;
	insert #el90 (LogDate, ProcessInfo, log_text) exec xp_readerrorlog 2;
	insert #el select log_text, log_date from #el90
end
else
begin
	insert #el80 (log_text, cont_row) exec xp_readerrorlog;
	insert #el80 (log_text, cont_row) exec xp_readerrorlog 1;
	insert #el80 (log_text, cont_row) exec xp_readerrorlog 2;
	insert #el select log_text, log_date from #el80
end

select upper(convert(varchar(255), ServerProperty('ServerName'))) as svr, log_date, count(*) as err_count
from #el
where log_date is not null
and (log_text like '%taking longer%'
or log_text like '%Severity: 16%'
or log_text like '%Severity: 17%'
or log_text like '%Severity: 18%'
or log_text like '%Severity: 19%'
or log_text like '%Severity: 20%'
or log_text like '%Severity: 21%'
or log_text like '%Severity: 22%'
or log_text like '%Severity: 23%'
or log_text like '%Severity: 24%'
or log_text like '%could not%'
or log_text like '%failure%'
or log_text like '%autogrow%'
or log_text like '%killed%'
or log_text like '%SqlDump%'
or log_text like '%SQL Server is starting%'
or (log_text like '%CHECKDB%' and log_text not like '%without errors%' and log_text not like '% 0 errors%')
or log_text like '%victim%')
group by log_date
order by log_date desc;

drop table #el;
drop table #el80;
drop table #el90;