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.

  • Probably best answered in dba.stackexchange.com
    – Jim B
    Jan 24 '12 at 14:12
  • Thanks, I was unaware it even existed - will head over there for db specific issues in future! Shame when posting with tags like "sql-server" "backup" and "transaction-log" it didn't shout out to suggest I was in the wrong place. If it did I missed it.
    – Chris
    Jan 25 '12 at 10:37

You do not have that kind of granular control on what goes into the transaction log. You could possibly flip back and forth between FULL and BULKINSERT modes. But, there is risk, it is an additional thing that you would need to manage and I wouldn't do it.

Backing up the transaction log will not shrink the size of the transaction log. You can manually shrink the transaction log. Repeated growth/shrink cycles tend to fragment the files and that is not recommended because of the negative performance impact.

If you want to control the size of the log backups, I would suggest that you:

  • Perform log backups more frequently than once every six hours. This will give you more files but they should be smaller on average. Most of the systems that I have run in full mode have had log backups taken every few minutes. If you have automated ways of handling the tlog backups, the increase in the number of files shouldn't be much of a problem. Note that you can set up more than one schedule for a SQL Agent job. For example, you could specify one tlog backup per six hours when things are slow, and once every ten minutes when things are busy.

  • Look into compressing your backup files. Some recent versions of SQL Server have backup file compression built-in and you can always go with a third-party product like SQL Lightspeed.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.