5

I encountered a production database of 12 GB with a log of 90 GB. Full and differential database backups are made with a third-party backup tool, but no one is backing up the transaction log so it got way out of hand in the past year.

Now I want to fix the situation by first doing a full backup from SQL Server and then doing a transaction log backup. There is not much space on the server, so I'd like to know in advance how big the transaction log backup will be, approximately. I understand that the transaction log will only be truncated after backup, so I'm afraid the backup will be 90 GB as well and clogg the server.

So far I can't find any information on backup sizes, can someone explain what will happen size-wise? Thanks in advance!

Edit:
SQL Server version is Standard Edition, and the database recovery model is full.

Improve this question
5
  • You only need a full backup, that will (should) automatically reset the tx log.
    – TomTom
    May 31, 2013 at 10:43
  • @TomTom: so when I do a full backup, the transaction log will be truncated automatically?? Or do you mean that we don't need a transaction log backup after doing a full backup anymore?
    – user5147
    May 31, 2013 at 10:52
  • What recovery model is the database in now, full or simple?
    – Cougar9000
    May 31, 2013 at 10:54
  • 7
    @TomTom - Unfortunately that's not the case. Paul Randal wrote a comprehensive blog post about this - sqlskills.com/blogs/paul/…
    – Stuart Moore
    May 31, 2013 at 10:55
  • I stand corrected. Interesting - very interesting.
    – TomTom
    May 31, 2013 at 10:59

2 Answers 2

Reset to default
4

If the logs are full, then yes you will probably be looking at needing the same space to backup them up.

If you're running SQL Server 2008 Enterprise then you may find that enabling backup compression will save you plenty of space, unfortunately the only way to really find out how much is to run the backup (do you have a dev environment where you could restore a copy to?).

The other option if you don't need the ability to restore to a point in Time previous to when you do this, would be to:

put the database into Simple Recovery mode Checkpoint put database back into Full Recovery Take Full backup Start taking log backups.

By putting the database into Simple Recovery you break the log chain, so when you move back to Full Recovery there will be no log as such for the first log backup to take. Don't forget to take the Full Backup so that SQL Server can start a new log chain.

As mentioned this will mean you'd lose the ability to recover the database to 15:25 21/10/2012 for example. But you'd still be able to restore to a full or differential backup.

Added 03/06/2013

Sorry, I completely forgot this option as well:

Do you have any remote storage options? You can just backup the transaction logs to a UNC path. This may be a bit slower depending on your network but it'll mean you'll have a full transaction backup covering the previous period and can then start taking smaller regular backups.

Improve this answer
2
  • Alas, it's Standard Edition. It might be fine to start a new log chain as you describe, because we have third-party full and differential backups. Thanks for the suggestion!
    – user5147
    May 31, 2013 at 10:50
  • This has my vote. After the full backup, set recovery model to simple and just shrink the log file and then set rm back to full. After that, the log backups will only be as big as the number of transactions between trn backups.
    – Cougar9000
    May 31, 2013 at 11:08
0

Even when you create a transaction log backup, the size of the online transaction log will remain the same. The transactions will be only marked for truncation, but will not be deleted from the file

When restoring a database from a full database backup, the LDF file size will remain the same as in the original one, though it's nearly empty. It's just inflated to its original size

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.