January 24, 2012 at 2:33 am
Hi all
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!
Not sure if it makes any difference but database mirroring is active, all backups etc are performed on the principal.
Thanks for your help
January 24, 2012 at 9:59 am
Few facts to remember.
1) Index rebuilds will cause significant logging and hence your log file will grow. Subsequently, the log backups will also grow. There is no way you can avoid the index rebuild transaction from being not logged in full recovery mode.
2) Backing up the log file more frequently will help you in keeping the log file size in check. Whether you take one log backup every 6 hours. Or 6 backups (one every hour) will not change the total size of the log backup files.
3) Always check the log_reuse_wait_desc column in sys.databases to find what is the reason the log file can not be reused. The most common reason is 'LOG_BACKUP' which means you need to take a log backup.
Recommendations.
1) Use a script that will rebuild/reorg only those indexes that are fragmented. This way you can reduce the number of ALTER INDEX commands, hence less transactions to log. Hence, smaller log files and smaller log backup files.
2) Schedule log backups more frequently, I have set up all my log backups jobs to run every 15 mins.
3) Check the log_reuse_wait_desc column to see the reason. Since you mentioned that the database is mirrored, I would be interested to see if that is causing any issues here.
http://saveadba.blogspot.com/2012/01/error-9002-severity-17-state-2.html
Blog
http://saveadba.blogspot.com/
January 24, 2012 at 10:06 am
Please read through this - Managing Transaction Logs[/url] and maybe http://www.sqlservercentral.com/articles/Transaction+Log/72488/
A log backup won't reduce the size of the log. It's not supposed to. It's supposed to make the space in the file reusable.
here are the components of a backup file (26.2GB):
Data 5GB
Indexes 1.2GB
Transaction Log 20GB
How are you determining what's in a backup file?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply