Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
You only need a full backup, that will (should) automatically reset the tx log. –  TomTom May 31 '13 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? –  Josien May 31 '13 at 10:52
    
What recovery model is the database in now, full or simple? –  Cougar9000 May 31 '13 at 10:54
6  
@TomTom - Unfortunately that's not the case. Paul Randal wrote a comprehensive blog post about this - sqlskills.com/blogs/paul/… –  Stuart Moore May 31 '13 at 10:55
    
I stand corrected. Interesting - very interesting. –  TomTom May 31 '13 at 10:59
add comment

2 Answers

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.

share|improve this answer
    
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! –  Josien May 31 '13 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 '13 at 11:08
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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