May 21, 2008 at 8:38 am
Hi,
Can you please comment on the following approaches:
1. Backup Transaction log every hour, finished by daily full backup at midnight.
2. Daily full backup, followed by:
- BACKUP LOG MyDB WITH TRUNCATE_ONLY
- DBCC ShrinkFile(MyDB_Log, 1000)
In case of server failure, the max amount of data loss is 1 hour for approach #1 and 1 day for #2.
How about overall performance of the DB and the bloat of the physical file size of the transaction log file? Are the two methods comparable or could they be widely different?
More specifically, I would like to know if Truncate Log and DBCC ShrinkFile have any adverse effects and how / when they should be used.
Thaks in advance for any help.
May 21, 2008 at 9:15 am
#2 is a bad idea all around.
Shrinking a file that's going to grow again only encourage fragmentation. If you just leave it alone - it will grow to the size it "needs" to be, at which point your best bet is to leave it there (I'd actually make it somewhat bigger than even that size, "just in case"). Every time it has to grow - the growing introduces delays in processing (since things have to stop and wait for the growing to finish).
Backing up a transaction log doesn't backup the Transaction log FILE, so the size of the file is not directly related to the size of the backup. The committed transactions not yet truncated would be what gets backed up, so you could easily have a "large" transaction log, and tiny transaction log backups.
If you don't want to have to back up the transaction log, then there's no reason to keep it on FULL recovery mode. Just set it to SIMPLE, and the transactions will automatically truncate.
The only time I use truncate log is when I've had a bad query, and my log drive is literally out of disk space altogether. I OCCASIONALLY have had to run the shrink, if once again a bad query has make the log grow out of control. I wouldn't run that regularly though for the reasons previously stated.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 21, 2008 at 9:27 am
Most including MS recommend against using the TRUNCATE_ONLY option. In fact it is being deprecated.
Your backup strategy needs to be defined based on the business needs the database meets. If the database is a reporting database that receives data from source system(s) and can be re-loaded fairly easily than daily or even weekly backups are probably enough. If the database is a business critical transactional database where data loss would cause damage to the business then at least hourly log backups should be taken. Also remember that you need to take into account how long restoring will take when planning for disaster recovery. So you may want to also look at using differential backups to reduce the number of transaction log backups you need to restore.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 21, 2008 at 9:44 am
Thank you very much for your quick replies. I think I know pretty well the back up strategy (full, differential, log). I wanted to know more about the practical experience on Truncate Log + ShrinkFile.
Matt Miller's detailed answer is very useful. Appreciated very much.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply