In my nightly maintenance plan, I was doing everything - reorganising and rebuilding indexes, updating statistics, etc. There's an hourly maintenance plan that does a transaction log backup.
I noticed that the transaction log backup immediately after the nightly rebuild was huge - and a little research told me that was because rebuilding indexes etc were logged operations.
I don't want to burden my backup system by backing up logs of index rebuilds. Therefore, I plan to change by Nightly plan to:
- Rebuild Indexes, Update stats, etc
- DUMP TRANSACTION WITH NO_LOG (oh, which is apparently called BACKUP LOG test WITH TRUNCATE_ONLY these days)
- Shrink log files
- Do full database backup
- Is there something wrong with this plan?
- What's the best way to include the log truncation in a maintenance plan?
- I just read that including both Reorganise and Rebuild indexes is redundant. How should I decide which is the right one to use in a nightly plan?