March 19, 2014 at 5:39 am
Hi,
We have many customers with a large database, many workers and many database transactions. Every night the databases are "full backupped". Regularly the logfiles (dbname_log.ldf) grow very large, for example a database (dbname.mdf) of 60GB and a logfile equal in size. When the drive becomes too full, I truncate these logfiles to free diskspace.
Question: is it a good decision to run a script that, after the daily full backup, truncates the logfile if it is larger than for example 10GB?
Thx,
Raymond
March 19, 2014 at 6:01 am
Full backup will not clear the log files. Full backup will take only the data backup.
Schedule for a log backup every "X" minutes and that "X" has to be minimal data loss acceptable by the company. this will allow the log file to get reused without much growth.
Log backup will help you to recover the data to maximum.
Regards
Durai Nagarajan
March 19, 2014 at 6:06 am
No, truncating the logs is not the way to go. What you need to determine is what amount of data you can afford to lose. This will drive you towards just using the FULL backups and switching your recovery model to Simple so that you don't need to do log backups at all. In which case, you set the log to be big enough to support the transactions on the system and leave it there (no shrinking over and over or truncating). Or, you need to be able to recover to a point in time, so you need to be running log backups every few minutes (as the poster above said). The log backups will reclaim space in the log. That's the right way to go about this. Full Recovery = Log Backups. Simple Recovery = No log backups. Full Recovery = Recovery to a point in time. Simple Recovery = You can only ever restore the full backup (+ a differential if you take those).
"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
March 19, 2014 at 7:48 am
No. Very bad idea, not the correct way to manage a log at all.
Please take a read through this: http://www.sqlservercentral.com/articles/Administration/64582/
Edit: There's also a stairway series on log management that goes into a lot more detail: http://www.sqlservercentral.com/stairway/73776/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2014 at 8:01 am
Hey guys,
Thanks for all the feedback. Got to read it all and see what to do...
Thx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply