Current major problem with backup sizes - I have been through many questions but am finding conflicting scenarios. I have multiple databases with the same problem but using one as an example, here are the components of a backup file (26.2GB):
- Data 5GB
- Indexes 1.2GB
- Transaction Log 20GB
I am backing up the transaction log every 6 hours, this is 1 database of about 6 that drives a reasonably high volume web application. Each Sunday we rebuild the indexes, it seems that every time that happens, the transaction log goes up by another 5%. Also, the backup of the transaction log is not reducing its size by much at all.
The system needs the capability to point-in-time restore between backups, what's more it seems that rebuilding indexes is making the transaction log jump up in size, I don't really see a major benefit in indexes being backed up, or held in the transaction log given they can be built on restore. Can this be cirumvented?
Ultimately, I am looking for a very low transaction log backup size for operational use that simply facilitates inter-backup point in time restore. I don't think I want to have indexes in my transaction log, and I'm not fussed about them being in my backup files - is that naive? Also I would like to know how to manage the size of that transaction log better - should I be backing it up more often, is there a reason it doesn't reduce in size?
I maybe missing the point here or looking for utopia but I am now in desperate need of help with this strategy!
EDIT: Not sure if it makes any difference but database mirroring is active, all backups etc are performed on the principal.