Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using xp_ReadErrorLog in SQL Server 2005

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2008-05-12 : 07:48:59

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.



Read Using xp_ReadErrorLog in SQL Server 2005

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-05-17 : 18:03:54
I have found that this proc also takes params 5 and 6, both datetimes. these can be used to fetch log entries within a certain time range. example:

xp_ReadErrorLog 0, 1, 'Failed', 'login', '2008-05-17', '2008-05-18'

definitely something you don't want to rely on though, as this proc is undocumented, as you say in the article.


elsasoft.org
Go to Top of Page

AndyDoran
Starting Member

1 Post

Posted - 2008-05-21 : 04:56:50
And just to be completely and utterly thorough, there is a 7th parameter which determines the search order:

xp_ReadErrorLog 0, 1, 'Failed', 'login', '2008-05-17', '2008-05-18', 'desc'
xp_ReadErrorLog 0, 1, 'Failed', 'login', '2008-05-17', '2008-05-18', 'asc'
Go to Top of Page

katesl
Starting Member

1 Post

Posted - 2008-05-21 : 10:13:30
--I like this better than using the UI; this is how I greet every SQL Server I admin

--drop table ##errorlog
create table ##errorLog (seq int identity(1,1), message varchar(255),continuationRow tinyint)
delete ##errorlog
insert into ##errorlog exec master..xp_readerrorlog

select * from ##errorlog
--where message like '%autogrow%'
order by seq desc

--start a new log when the log has many records
--dbcc errorlog
--and save the archive log in a table in the admin db
--use admin
--create table errorLog20080406x20080520 (seq int identity(1,1), message varchar(255),continuationRow tinyint)
--insert into errorLog20080406x20080520 exec master..xp_readerrorlog 1



Kate Luxemburg
Go to Top of Page

danmcclain
Starting Member

2 Posts

Posted - 2008-05-21 : 17:23:57
Please keep in mind this article is related to SQL Server 2005.
Katesl, I do like your method for SQL2000 and have used it.
Your method also gives for a more flexible search of the error log entries, but also keep in mind that the columns being returned from xp_ReadErrorLog have changed from SQL2000.

SQL2000 columns from xp_ReadErrorlog = ERRORLOG, ContinuationRow
SQL2005 columns from xp_ReadErrorlog = LogDate, ProcessInfo, Text

Also note that using the extended stored procedure you have no temporary table usage.
Go to Top of Page

contrari4n
Starting Member

27 Posts

Posted - 2008-06-20 : 09:22:16
I use a similar idea to identify instances with potential problems without actually displaying the errors.

What I want is something like this

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

So I can instantly see the number of errors and other interesting events that have occurred recently on each instance. These can then be investigated individually.

The TSQL for this is:
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;


This looks at the last 3 logs only, and I cycle my error logs at midnight every day, so this gives me results for the last 3 days (or less if the service has been restarted or failed over).

I call this from an HTA script that cycles through all my servers and summarises the results. If anyone is interested in the HTA it is at [url]www.sql-server-pro.com/sql-server-monitoring-part-2.html[/url].


Richard
http://www.sql-server-pro.com
Go to Top of Page

vvkp
Starting Member

12 Posts

Posted - 2008-09-22 : 16:16:02
Really good one.
But what I want is to read the SQL Error lOgs for the last one hours data only. We have Megabytes size in current file.
So is there any way to read the lines for the last one hour time from the current error log file?

Thanks in advance,
vvkp
Go to Top of Page

danmcclain
Starting Member

2 Posts

Posted - 2008-11-06 : 08:17:05
You can easily read the last hour or any time frame of the error log using the xp_Readerrorlog. Here is an example for reading the last one hour of the current SQL error log.
set nocount on
Declare @CurrentDate datetime
Declare @DatelessHour datetime
Set @CurrentDate = getdate()
Set @DatelessHour = DATEADD(hh,-1,@CurrentDate)
EXEC xp_ReadErrorLog 0, 1, NULL, NULL, @DatelessHour, @CurrentDate
Go to Top of Page

vvkp
Starting Member

12 Posts

Posted - 2008-11-06 : 10:50:40
WoW...really great...!
I got what I want.
Thanks a LOT for your help Mr. DanMcClain
Go to Top of Page
   

- Advertisement -