May 13, 2008 at 9:26 am
Question for those who understand the transaction log growth size.
I backed up the transaction log from 8am to 8pm daily (Monday thru Friday).
Every morning, for instance Tuesday Morning or Wednesday, etc., the 1st Transaction log backup is almost the size of the full backup. Any transaction log backups afterwards is very normal (small size).
Everyone has informed me the business hours is closed around 6pm so there should be no active transactions. The only thing that I have is Daily Reindexing and Daily Update Statistics.
Would changing the Recovery model of the database during the Reindexing and Daily Update to either Simple or Bulk Logg help? And then change it back to Full?
May 13, 2008 at 9:32 am
Reindexing will definitely grow the log file. I would just run my full backup and a TX Log backup after the reindexing job
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 14, 2008 at 10:02 am
I did what you say : Run a Full Backup afterwards and then run a transactionlog backup.
That transactionlog backup is still very big.
What about turning the database to simple mode while re-indexing operation is on.
And then turn back the database to Full mode and then do a Full backup and then a transaction log backup?
May 14, 2008 at 1:03 pm
What exactly are you trying to accomplish? The maintenance jobs grow the t-log as needed. Are you simply trying to save disk space? I'd continue doing things the way you are as long as disk is not your issue. Personally, I run my maintenance jobs and then do a full backup (in case I need to restore to that point, all my work is done) and then resume my scheduled t-log backups.
-- You can't be late until you show up.
May 15, 2008 at 11:14 am
I'm trying to reduce the Transaction backup size. The 1st Transaction Log Backup after the full Backup which is after the Reindexing job is huge.
This maybe a problem when it comes to recovering the database because the Transaction log backup is almost the same size as the Full backup which will take much longer to recover.
Do you get my point now?
May 15, 2008 at 11:23 am
Why not take your full backup after your index rebuild and log backup? Then, if you do need to recover, simply restore the full (and potentially other, smaller t-logs) and your maintenance job work is already complete (and restored). That's what I've been doing for years and it works well (for me!). I don't like toggling recovery modes on a production database unless I have very compelling reasons to do so and no other alternatives.
-- You can't be late until you show up.
May 15, 2008 at 11:26 am
Or, if you don't want to toggle recovery modes but also want to reduce your backup size, you could truncate the log without backing up, after reindexing. Of course, this would mean that you wouldn't be able to recover to a point between the last log backup before the reindex and the first full backup after it.
John
May 15, 2008 at 11:34 am
Good point, didn't even think about that option. And it saves disk space!
-- You can't be late until you show up.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply