October 27, 2008 at 1:08 pm
Hello
I am able to view only current error log files, I can't view any archieve error log file in sql server 200 sp4.
I can create new error log files by sp_cycle_errorlog, but how can I set up automatically so I can view archieve file also.
please let me know
October 27, 2008 at 1:23 pm
Well... I guess it is undocumented (in bol), so use carefully.
You could use master..xp_readerrorlog
I haven't got all info at hand, but if you search the net, you'll find it for sure.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 27, 2008 at 1:42 pm
do you mean that i need to run this extended procedure to overcome this issue.
October 27, 2008 at 1:51 pm
well my question is, how to generate multiple sql error log automatically. Everytime I do have a current error log, there are no archaieve logs. And my current error log is growing in size and making server slow. so how do i creagte an error log file of small size.
thank you
October 27, 2008 at 2:04 pm
did you check in EM configuration of the error logs to make sure the number of logs history isnt set to 0
October 27, 2008 at 2:09 pm
where do i check that in EM, i couldnt locate.
October 27, 2008 at 2:18 pm
I didn't realize you actually wanted to consult it with EM. Sorry for that:blush:
With EM Management, SQLServer errorlogs then right click and check that setting.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 27, 2008 at 2:35 pm
I went to sql server log configuration and put the maximum error files upto 6. But my question is how to make smaller size of error log files, because my sql error log file keeps on growing more than 100 mega bytes. is there anyway that if file reaches upto 1 mb archieve that and it will automatically archieve that 1mb file and create new error log file.
thank you
October 27, 2008 at 2:47 pm
abhisek karki (10/27/2008)
I went to sql server log configuration and put the maximum error files upto 6. But my question is how to make smaller size of error log files, because my sql error log file keeps on growing more than 100 mega bytes. is there anyway that if file reaches upto 1 mb archieve that and it will automatically archieve that 1mb file and create new error log file.thank you
You’ll have to write your own code that cycles that error log and run it as a job. You can use sp_cycle_errorlog to archive the current log and create a new one. I usually just do it according to time interval instead of checking the size of the log.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 28, 2008 at 1:06 am
we cycle the errorlog every sunday using a sqlagent job.
If you want to cycle, based on the file size, you'll have to write your own proc.
e.g. this one monitors sqlagent.out primitive but effective !
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc spc_ALZDBA_Check_SQLAgent_Out
as
begin
set nocount on
if exists (select 1
from msdb.dbo.sysalerts a (nolock)
left join msdb.dbo.sysjobs j (nolock)
on a.job_id = j.job_id
left join master.dbo.sysmessages s (nolock)
on a.message_id = s.error
where a.message_id > 49999 -- enkel user-made alerts
and a.enabled = 1
and j.enabled = 0 )
begin
-- Opvragen SQLAGENT.OUT info via DOS dir
--
create table #tmpAgentInfo (auto_start int not null
, msx_server_name varchar(128) null
, sqlagent_type int not null
, startup_account varchar(128) null
, sqlserver_restart int not null
, jobhistory_max_rows int not null
, jobhistory_max_rows_per_job int not null
, errorlog_file varchar(500) not null
, errorlogging_level int not null
, error_recipient varchar(128) null
, monitor_autostart int not null
, local_host_server varchar(128) null
, job_shutdown_timeout int not null
, cmdexec_account varchar(128) null
, regular_connections int not null
, host_login_name varchar(128) null
, host_login_password varchar(128) null
, login_timeout int not null
, idle_cpu_percent int not null
, idle_cpu_duration int not null
, oem_errorlog int not null
, sysadmin_only int not null
, email_profile varchar(128) null
, email_save_in_sent_folder int not null
, cpu_poller_enabled int not null
)
insert into #tmpAgentInfo
EXECUTE msdb.dbo.sp_get_sqlagent_properties
Declare @StartDir varchar(500)
Select @StartDir = errorlog_file
from #tmpAgentInfo
-- print @StartDir
drop table #tmpAgentInfo
create table #T_Dir_info (
VlgNr int not null identity(1,1)
, Niveau integer not null
, FileDate datetime not null
, Grootte bigint not null
, Folder varchar(5000) not null)
create table #T_Dir_info_relations (
VlgNr int not null identity(1,1)
,ParentVlgNr integer
, FileVlgNr integer)
create table #T_cmdshell (Dir varchar(5000))
set nocount on
declare @Dir_wrk varchar(5000), @RijenVWK smallint , @VlgNr int, @FileVlgNr int
Declare @Niveau integer , @FileDate datetime, @Grootte bigint , @Folder varchar(5000), @FilePrefix varchar(5000)
Select @Niveau = 0, @Grootte = -2 , @Folder ='', @FilePrefix = @StartDir
declare @cmd varchar(5000)
WHILE @Niveau < 999999
BEGIN
truncate table #T_cmdshell
set @RijenVWK = 0
if @Niveau = 0
begin
set @cmd = 'dir ' + @StartDir + ' /-C'
insert into #T_cmdshell
EXEC master..xp_cmdshell @cmd
--27/08/2004 21:59 A10_Demo
declare csrDirCmd cursor for
Select *
from #T_cmdshell
where isdate(substring(Dir,7,4) + '-' +substring(Dir,4,2) + '-' + substring(Dir,1,2) ) = 1
-- dir like '%Documents and Settings%'
for read only
open csrDirCmd
FETCH NEXT FROM csrDirCmd
INTO @Dir_wrk
WHILE @@FETCH_STATUS = 0
BEGIN
set @RijenVWK = 1
-- print substring(@Dir_wrk,7,4) + '-' +substring(@Dir_wrk,4,2) + '-' + substring(@Dir_wrk,1,2) + substring(@Dir_wrk,12,6)+ ':00'
-- print ltrim(replace(substring( @Dir_wrk,20,15),'.',''))
select @FileDate = convert(datetime, substring(@Dir_wrk,7,4) + '-' +substring(@Dir_wrk,4,2) + '-' + substring(@Dir_wrk,1,2) + substring(@Dir_wrk,12,6)+ ':00')
, @Grootte = case when @Dir_wrk like '% %' then (-1) else convert(bigint, ltrim(replace(substring( @Dir_wrk,20,16),'.',''))) end
, @Folder = @FilePrefix + substring(@Dir_wrk,37,datalength(@Dir_wrk))
-- print @FileDate
-- print @Grootte
-- print @Dir
Insert into #T_Dir_info (Niveau, FileDate, Grootte, Folder )
values (@Niveau, @FileDate, @Grootte , @Folder)
-- Volgende rij inlezen
FETCH NEXT FROM csrDirCmd
INTO @Dir_wrk
END
-- Cursor afsluiten
CLOSE csrDirCmd
DEALLOCATE csrDirCmd
end
else
begin
declare csrDirParent cursor for
Select Folder , Vlgnr
from #T_Dir_info
where Niveau = @Niveau - 1
and grootte = (-1)
for read only
open csrDirParent
FETCH NEXT FROM csrDirParent
INTO @cmd, @VlgNr
WHILE @@FETCH_STATUS = 0
BEGIN
Set @FilePrefix = @cmd + '\'
-- print convert(varchar(15),@Niveau) +' - ' + @FilePrefix
set @cmd = 'dir "' + @cmd + '" /-C'
-- print @cmd
truncate table #T_cmdshell
insert into #T_cmdshell
EXEC master..xp_cmdshell @cmd
--27/08/2004 21:59 A10_Demo
-- select * from #T_cmdshell
declare csrDirCmd cursor for
Select *
from #T_cmdshell
where isdate(substring(Dir,7,4) + '-' +substring(Dir,4,2) + '-' + substring(Dir,1,2) ) = 1
and Dir not like '% .%'
for read only
open csrDirCmd
FETCH NEXT FROM csrDirCmd
INTO @Dir_wrk
WHILE @@FETCH_STATUS = 0
BEGIN
set @RijenVWK = 1
-- print substring(@Dir_wrk,7,4) + '-' +substring(@Dir_wrk,4,2) + '-' + substring(@Dir_wrk,1,2) + substring(@Dir_wrk,12,6)+ ':00'
-- print ltrim(replace(substring( @Dir_wrk,20,15),'.',''))
select @FileDate = convert(datetime, substring(@Dir_wrk,7,4) + '-' + substring(@Dir_wrk,4,2) + '-' + substring(@Dir_wrk,1,2) + substring(@Dir_wrk,12,6)+ ':00')
, @Grootte = case when @Dir_wrk like '% %' then (-1) else convert(bigint, ltrim(replace(substring( @Dir_wrk,20,16),'.',''))) end
, @Folder = @FilePrefix + substring(@Dir_wrk,37,datalength(@Dir_wrk))
-- print @FileDate
-- print @Grootte
-- print @Dir
Insert into #T_Dir_info (Niveau, FileDate, Grootte, Folder )
values (@Niveau, @FileDate, @Grootte , @Folder)
Set @FileVlgNr = SCOPE_IDENTITY( )
insert #T_Dir_info_relations (ParentVlgNr, FileVlgNr )
values ( @VlgNr, @FileVlgNr)
-- Volgende rij inlezen
FETCH NEXT FROM csrDirCmd
INTO @Dir_wrk
END
-- Cursor afsluiten
CLOSE csrDirCmd
DEALLOCATE csrDirCmd
-- Volgende rij inlezen
FETCH NEXT FROM csrDirParent
INTO @cmd, @VlgNr
end
CLOSE csrDirParent
DEALLOCATE csrDirParent
end
if @RijenVWK = 0
begin
Set @Niveau = 999999
end
else
begin
Set @Niveau = @Niveau + 1
-- if @Niveau > 10
-- begin
-- Set @Niveau = 999999
-- end
end
end
-- Select *
-- from #T_Dir_info
-- where Niveau = 0
if exists (
Select count(*) as aantal_files
, convert(decimal(15,2),
(convert(decimal(15,2),sum(Grootte))
/1024 / 1024 )
) as Grootte_Mb
from #T_Dir_info
having convert(decimal(15,2),
(convert(decimal(15,2),sum(Grootte))
/1024 / 1024 )
) > 9
)
begin
DECLARE @subject varchar(250)
select @subject = 'Dringend ! ' + @@servername + ' SQLAGENT.OUT file veel ter groot [' + cast(isnull(Grootte_MB,-1) as varchar(10)) + '] MB.'
from (Select count(*) as aantal_files
, convert(decimal(15,2),
(convert(decimal(15,2),sum(Grootte))
/1024 / 1024 )
) as Grootte_MB
from #T_Dir_info
having convert(decimal(15,2),
(convert(decimal(15,2),sum(Grootte))
/1024 / 1024 )
) > 9
) A
exec dbo.sp_ALZ_SQLSMTPMail @recipients ='myemailadres',
@message = @subject,
@subject = @subject,
@query = '',
@copy_recipients = null,
@blind_copy_recipients = null,
@width = null,
@separator = ' ',
@Headers = 0
end
--clean up
drop table #T_Dir_info
drop table #T_Dir_info_relations
drop table #T_cmdshell
end
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply