patternsqlMinor
How large will the Transaction Log backup be?
Viewed 0 times
thelogwilllargetransactionhowbackup
Problem
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#43518, answer score: 4
Revisions (0)
No revisions yet.