Maintenance Job Increases Transaction Logo Size

Question

Maintenance Job Increases Transaction Logo Size

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

You don't have to perform a backup after switching between bulk-logged and full recovery models. Now, if you switch to simple - it will break the log chain and you will need to perform a full backup to restart it.

However, doing this will only reduce the amount of logging done in the transaction log. All changes will still be backed up - and your first transaction log backup after the reindexing will be just as large as it is now. And, if you are not rebuilding - but are actually performing a reorganization, switching recovery models won't help at all because reorg is not a bulk-logged operation.

So, if you want to switch - you can do that and reduce the actual size of the transaction log. But, again - your backup files will not be reduced in size at all.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

george sibbald SSC Guru Points: 104210 More actions · Answer 2

personally I would stick the full backup just after the integrity check, then do the rest of the operations.

by the way you probably do not need to reindex every night. I seriously suggest you look into the script from BOL which will only rebuild indexes that are actually fragmented. As you have a maintenance window don't bother with the reorganise as that will be slower and produce more log.

There are scripts on this site which incorporate transaction log backups into the reindex job.

---------------------------------------------------------------------

Brian Fischer Old Hand Points: 367 More actions · Answer 3

george sibbald-364359 (11/12/2009)
personally I would stick the full backup just after the integrity check, then do the rest of the operations.
by the way you probably do not need to reindex every night. I seriously suggest you look into the script from BOL which will only rebuild indexes that are actually fragmented. As you have a maintenance window don't bother with the reorganize as that will be slower and produce more log.
There are scripts on this site which incorporate transaction log backups into the reindex job.

I'll take a peek then at BOL and those scripts you mentioned. I was mainly following the guidelines/recommendations of a vendor of ours that walked me through step by step on setting up the database with simple recovery model and running a maintenance plan each night that checks integrity, rebuild indexes, and then performs a full backup. I didn't like the fact that they suggested simple recovery on a database with a lot of transactions during the day, so I ventured out to learn more and apply the full recovery model. Which led me to figure out how I'm going to tranlog backups with the rest of the maintenance plan.

Thanks again for helping me to figure things out. All good info!