2

Can you let me know why a transaction log would be a lot smaller in size after restoring from a full backup. Currently we have a massive log file on the live server, but after a restore to a test / development server the log file is considerably smaller. Im guessing this is because the log was truncated as part of the backup however why would the original log file still be so huge.

Improve this question

3 Answers 3

Reset to default
1

but after a restore to a test / development server the log file is considerably smaller

Because when you're creating a full backup you're creating a backup of the data (as it is physically and as it is materialized from the log). Restoring it somewhere else restores it as data, not log.

Im guessing this is because the log was truncated as part of the backup however why would the original log file still be so huge.

The original log file might be truncated so the space can be reused, but it's size might stay the same.

Improve this answer
2
  • looking at the db its set to full recovery but has no transaction log backups set. I presume that by setting up some trans backups this will truncate the log. Can i then run a shrinkfile to reduce the actually file size
    – Richard Banks
    Feb 15, 2012 at 12:02
  • @RichardBanks It might work, have a look at msdn.microsoft.com/en-us/library/ms178037.aspx
    – Andreas Ågren
    Feb 15, 2012 at 12:05
2

A full database backup contains almost no transactions - just the transactions that happened while the FULL transaction log was being created. The LDF file created when a database is restored using a full database backup is almost empty, regardless its size. It should be inflated (with no data but active portion of transaction log) to its backed up size when restoring.

If you want to restore the transaction log file too, the best way to do that is to restore the database using its LDF and LDF files (attach them), instead of using a full database backup.

There are many reasons why the original file is huge:

  • The database is in FULL recovery model and you don't create transaction log backups at all
  • The database is in FULL recovery model and you don't create transaction log backups often enough
  • there are large-scale index operations

For any of the possible causes, creating transaction log backups regularly should keep the size of the LDF file.

Improve this answer
0

After Backup the logs are taken back to the start as the database is newly recorded Try Reading this link it may help you the : http://msdn.microsoft.com/en-us/library/ms190440.aspx

Read How a log backup are used

Improve this answer
1
  • But the question was regarding a full backup.
    – Andreas Ågren
    Feb 15, 2012 at 11:59

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.

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