How to Archive Error log????

Question

Post reply

How to Archive Error log????

Bhuvnesh

SSC Guru

Points: 59351
More actions
December 24, 2008 at 12:10 am

#125989

Hi,
On one of my server , the error/event log has become very heavy , i guess it contains millions of records because of which it takes couple of minutes to open it. i know if i restart the server i will overcome with this problem but i can't do it ..is there any alternative to archive this log ????
Please help me
bhuvnesh
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Minaz Amin SSChampion Points: 11052 More actions · Answer 1

I think you should use sp_cycle_errorlog to refresh the error log without restarting the SQL Server.

"More Green More Oxygen !! Plant a tree today"

SQLRockstar SSCertifiable Points: 7086 More actions · Answer 2

we have a job in sql agent that runs every hour and checks the size of the error log. if the log size gets over 2mb, we recycle. i got the idea and code from steve jones a few years ago. here is one of the jobs we run, scripted out. load this to your server and make any alterations you want.

USE [msdb]

GO

/****** Object: Job [DBCC Errorlog] Script Date: 12/24/2008 08:55:03 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [DBA Job] Script Date: 12/24/2008 08:55:03 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA Job' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA Job'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBCC Errorlog',

@enabled=1,

@notify_level_eventlog=2,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Cycle Errorlog.',

@category_name=N'DBA Job',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [dbcc errorlog] Script Date: 12/24/2008 08:55:04 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'dbcc errorlog',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=1,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'declare @limit float

set @limit = 2000000

create table #logs

( Lognum int

, Logdate datetime

, Size float

)

insert into #logs exec master..xp_enumerrorlogs

if ( select size from #logs where LogNum = 0) > @limit

begin

dbcc errorlog

end

drop table #logs

',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'sched',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=8,

@freq_subday_interval=1,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20041104,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

----------------------
https://thomaslarock.com

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

One thing I would recommend is that you change the number of logs to be retained by the system. The default is only 6, so you will lose information really quickly if you leave it the default. You can do this in SSMS by right clicking the SQL Server Logs Node under Management and selecting Configure. Or you can do it with a script using:

USE [master]

GO

DECLARE @NumLogs int

SET @NumLogs = 30

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, @NumLogs

GO

How to Archive Error log????

Cookies on SQLServerCentral