Tell me more ×
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.
up vote 2 down vote favorite

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.

share|improve this question

migrated from stackoverflow.com Feb 16 '12 at 0:56

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
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 '12 at 12:02
@RichardBanks It might work, have a look at msdn.microsoft.com/en-us/library/ms178037.aspx – Andreas Ågren Feb 15 '12 at 12:05
up vote 0 down vote

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

share|improve this answer
But the question was regarding a full backup. – Andreas Ågren Feb 15 '12 at 11:59
up vote 0 down vote

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.

share|improve this answer

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.