SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using xp_ReadErrorLog in SQL Server 2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/12/2008 :  07:48:59  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

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
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 05/17/2008 :  18:03:54  Show Profile  Visit jezemine's Homepage  Reply with Quote
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 Posts

Posted - 05/21/2008 :  04:56:50  Show Profile  Reply with Quote
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 Posts

Posted - 05/21/2008 :  10:13:30  Show Profile  Reply with Quote
--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

USA
2 Posts

Posted - 05/21/2008 :  17:23:57  Show Profile  Reply with Quote
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

United Kingdom
27 Posts

Posted - 06/20/2008 :  09:22:16  Show Profile  Visit contrari4n's Homepage  Reply with Quote
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 www.sql-server-pro.com/sql-server-monitoring-part-2.html.


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

vvkp
Starting Member

12 Posts

Posted - 09/22/2008 :  16:16:02  Show Profile  Send vvkp a Yahoo! Message  Reply with Quote
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

USA
2 Posts

Posted - 11/06/2008 :  08:17:05  Show Profile  Reply with Quote
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 - 11/06/2008 :  10:50:40  Show Profile  Send vvkp a Yahoo! Message  Reply with Quote
WoW...really great...!
I got what I want.
Thanks a LOT for your help Mr. DanMcClain
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000