January 17, 2007 at 7:54 am
I'm having a problem where a ton of 318 error jumped my error log up to 1.8 GB in size. Is there a way to have the log files rotate every so often or limit the file size where the oldest events are purged to keep the server from locking up? I've tried just stopping the sql server agent service but it seems that the sql server service is using this file as well. I want to be able to archive this file elsewhere. Thanks for any replys.
January 17, 2007 at 9:22 am
You need to backup your transaction log periodically to purge old tranasctions. The frequency of the backup depends on the amount of activity in your database. Another option is to switch to Simple recovery mode, but this is not something you should do in a production environment.
January 18, 2007 at 4:29 am
We usually run this once a week on production servers:
sp_cycle_errorlog
We generally only keep the four weeks around. If you want to archive them after you cycle them, you can set up some sort of backup routine to tape or something.
This works on 2000 & 2005 servers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 18, 2007 at 7:30 am
thanks Grant we'll have to do that.
January 18, 2007 at 11:45 am
Rather than just cycle the error log on a fixed timed interval, I setup a job on each server that conditionally cycles the error log based upon age and/or size. On SQL Server 7.0 the size of the error log isn't easily available so I cycle those only based upon the age of the errorlog. On SQL Server 2000 I cycle the log when it passes a certain age or 1MB size. Here is the script I use for the job:
set nocount on
declare @CycleMessage varchar(200)
select @Cyclemessage = ''
-- Detect SQL Server Version because xp_enumerrorlogs is different between 7.0 and 2000/2005
if exists (select 1 from tempdb..sysobjects where name like '#SQL_VERSION%')
drop table #SQL_VERSION
create table #SQL_VERSION (IndexID int, Name varchar(256), Internal_Value int, Character_Value varchar(256))
insert into #SQL_VERSION (IndexID, Name, Internal_Value, Character_Value)
exec master..xp_msver
if exists (select 1 from #SQL_VERSION where Name like 'ProductVersion%' and Character_Value like '7.%')
begin
-- SQL Server 7.0 - Load file statistics of ErrorLogs and determine if log needs cycled.
If exists (select 1 from tempdb..sysobjects where name like '#ErrorLogs7%')
Drop Table #ErrorLogs
CREATE TABLE #ErrorLogs7 (ArchiveNumber tinyint, DateCreated Datetime)
INSERT into #ErrorLogs7 (ArchiveNumber, DateCreated)
EXEC master.dbo.xP_enumerrorlogs
select @Cyclemessage = 'The current SQL Server ErrorLog was created on ' + convert(varchar, DateCreated , 101) +'.'
+ case when DateCreated <= dateadd(dd, -14,getdate()) then ' The ErrorLog will now be cycled because of its age.'
else ' The ErrorLog does not need to be cycled.' end
from #ErrorLogs7 where ArchiveNumber = 1
print @CycleMessage
end
If exists (select 1 from #SQL_VERSION where Name like 'ProductVersion%' and (Character_Value like '8.%' OR Character_Value like '9.%'))
Begin
-- SQL Server 2000(+) - Load file statistics of ErrorLogs and determine if log needs cycled.
If exists (select 1 from tempdb..sysobjects where name like '#ErrorLogs%')
Drop Table #ErrorLogs
CREATE TABLE #ErrorLogs (ArchiveNumber tinyint, DateCreated Datetime, LogFileSizeBytes int)
INSERT into #ErrorLogs (ArchiveNumber, DateCreated, LogFileSizeBytes )
EXEC master.dbo.xP_enumerrorlogs
select @Cyclemessage = 'The current SQL Server ErrorLog was created on ' + convert(varchar, DateCreated , 101) + ' and is using ' +
case when LogFileSizeBytes 1048575 then cast(cast((cast(LogFileSizeBytes as money)/1024.0)/1024.0 as money )as varchar(10)) + ' MB of disk space.'
else cast(LogFileSizeBytes as varchar(16)) + ' Bytes of disk space.' end
+ case when LogFileSizeBytes > 1048575 then ' The ErrorLog will now be cycled because of its size.'
when DateCreated <= dateadd(dd, -14,getdate()) then ' The ErrorLog will now be cycled because of its age.'
else ' The ErrorLog does not need to be cycled.' end
from #ErrorLogs where ArchiveNumber = 1
print @CycleMessage
End
If @CycleMessage like '%will now be cycled%'
DBCC ERRORLOG
Finalize:
If exists (select 1 from tempdb..sysobjects where name like '#ErrorLogs7%')
Drop Table #ErrorLogs7
If exists (select 1 from tempdb..sysobjects where name like '#ErrorLogs%')
Drop Table #ErrorLogs
if exists (select 1 from tempdb..sysobjects where name like '#SQL_VERSION%')
drop table #SQL_VERSION
set nocount off
January 18, 2007 at 12:20 pm
Do you have a 2005 version of this script or will the 2000 work ?
January 18, 2007 at 12:38 pm
The "2000(+)" portion of the script will work for 2000 (8) or 2005 (9).
January 23, 2007 at 10:34 am
Why does the script use ArchiveNumber = 1 instead of ArchiveNumber = 0?
January 23, 2007 at 11:06 am
Wow, jyuan, I don't know. I must have written the script while sleep-deprived. Should be 0. Good catch...now I have to go fix the job on all my servers. 🙁
January 30, 2007 at 2:39 pm
Can you trigger the cycle error log based on the error log size?
Thanks,
February 6, 2007 at 10:06 am
jyuan,
Yes, on 2000 / 2005 databases the script above would be for cycling a log if it was over 14 days OR if it is over 1MB in size (LogFileSizeBytes > 1048575). As you previously mentioned, though, the references to "ArchiveNumber = 1" should be fixed to say "ArchiveNumber = 0".
On 7.0 database, the LogFileSizeBytes value is not provided by xp_enumerrorlogs, so cycling by size using this script is not supported.
September 1, 2009 at 9:49 am
Great bit of code here. Just had a single error log reach 14.5 GB. Killed our testing environment. owner found it when he came in and deleted it before I could take a look to find out what happened.
At least with this, I will still have the logs for future reference.
Fraggle.
September 2, 2009 at 11:38 am
Another approach is just to set up a scheduled task to perform this weekly.
This works well if you configure the number of SLQ Server errorlogs to 99.
We just use the command:
DBCC ERRORLOG
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply