SQL server logs - recycling
-
HI. How often should the sql server logs be recycled if you don't regulary stop/start your sql server?
Juanita
-
That really depends on how active the log is. For example, if you are taking transaction logs every 10-15 minutes, or are auditing successful logins, you might want to recycle it as often as every day.
Basically, if it starts becoming unwieldy, taking too long to load, consider recycling more often. If it is always very responsive, you're probably ok leaving it as is.
In my shop, a general rule of once per week is the norm, but adjustments are made to accomodate the needs of each server.
By the way, you can increase the number of logs that you keep by right clicking on "SQL Server Logs" and select "Configure..."
Steve
-
Thank you.. actually i did increase the number of logs. I might give the weekly timing a shot.
Thanks again,
Juanita
-
I would recycle you log files based on size of the log file. This is especially true if you are attempting to monitor your SQL Server remotely. Attempting to pull up an log file can that is too big can be pain full. I found this SP a while back that can then be called from a job that will cycle your errlog based on sizes. Of course I would base this on your own preferences and perhaps increase the number of log files as well.
Create Procedure procErrorLogMaintenance
@MaxFileSize int = 52428800 --- default Maximum error log size is 50MB
AS
BEGIN
set nocount on
Declare @CurrentLogFileSize bigint
Create Table #LogFileInfo
(
[Archive #] tinyint,
[Date] datetime,
[Log File Size (Byte)] bigint
)
Insert #LogFileInfo
Exec xp_enumerrorlogs
Select @CurrentLogFileSize = [Log File Size (Byte)]
From #LogFileInfo
Where [Archive #] = 0
If @CurrentLogFileSize > @MaxFileSize
BEGIN
print 'Rolling log files'
Exec sp_cycle_errorlog
END
else
BEGIN
print 'Current Log Files within limits, less than ' + convert(varchar (10), @MaxFileSize) + ' ON ' +
RTRIM(CONVERT(varchar(30), GETDATE())) + '.'
END
print ' '
print 'Log file sizes'
print ' '
select * from #LogFileInfo order by [Archive #]
Drop Table #LogFileInfo
END
----------------<<<<<<<<>>>>>>>>>>--------
-
Does it in any way hurt to recycle error logs while users are using sql server? i would schedule to recycle periodically on off hours but just curious if i tried during the day would it corrupt anything?
juanita
-
No it should not. However I would recycle the logs during off peak hours to give you a "clean-break" in the logs from other processing. This way you are not having to span multiple logs for set of related events.
-
Thank you very much to everyone. I've put a job in place to run weekly and i received alot of neat tips from this thread !!
Juanita
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply