May 8, 2009 at 2:13 am
Hi,
I normally check SQL Server Logs every morning, one of my Archive #1 -05/08/2009(1900002156byte) and when I click on the log, it tooks ages and not even come up. That is so big and all the Archive #2 to #5 are normal size. Anybody know why Logs is too big? I restart the SQL server this morning to recreate the new Log file. Any Idea?
any way can I remove that big log Archive?
Thanks.
Leo
May 8, 2009 at 2:23 am
one way to recycle logs is scheduling the script below once a week,
Exec sp_cycle_errorlog
-:-)
Cheer Satish 🙂
May 8, 2009 at 8:24 am
Hi,
Do you mean Archive Logs in SQL or ERROR Logs?
May 8, 2009 at 9:40 am
The SQL Server Error log keeps getting added to as long as SQL Server runs. It restarts when the instance restarts.
sp_cycle_error_log simulates the restart process. Errorlog becomes errorlog.1, errorlog.1 becomes errorlog.2, etc. And you get a new errorlog to check.
However, be aware that cycling this means that you could be losing data since SQL only keeps a certain number of logs. If you want to recycle this, I might recommend weekly and then increase the number of logs kept to 10 or 20.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 8, 2009 at 11:27 pm
If u want to limit the size log file , the sp_cycle_errorlog system stored procedure can be issued to start a new error log. Depending on the growth rate of the SQL Server error log dictates when sp_cycle_errorlog should be issued.
Please follow this example
EXEC master.sys.sp_cycle_errorlog;
-- Expected successful output
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Next, the easiest means to address this need would be to schedule a SQL Server Job to support the need. Reference the SQLServer2005_CycletheErrorLog_Job.txt as a point of reference.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply