Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cmspot
Starting Member
44 Posts |
 Posted - 2007-12-06 : 04:14:33
|
| Hello everybody ,I want you to explain me something that i find a little strange.Yesterday i had a problem with a huge log file and a friend in here helped me fix this by making a log back up and then shrinking it to 200 MB.Everything went fine.When i returned home i tried something else in a test environment..Here what happened:I Got a full database back up.I dropped the database.I created a new one with 1GB mdf and 250MB ldf.I vanished the log back -upI restored the mdf file on the new databaseThe log file again had the same huge size after the restore.How did this happen?I sell my mother in law.Is anybody interested? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-12-06 : 06:39:35
|
| This will be because the full backup will still hold all open transactions as the checkpoint has not occurred. |
 |
|
|
cmspot
Starting Member
44 Posts |
Posted - 2007-12-06 : 11:26:52
|
| But My mdf back up was about 500 mb and my log Back Up Was more than 5GB.I deleted my log back up.Where dit it find and restored 5GBs of data?I sell my mother in law.Is anybody interested? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-06 : 11:29:44
|
Are you only using 500 mb of the database file (even if the file itself was 5 gb originally)?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-06 : 17:54:36
|
quote:
Originally posted by cmspot
Here what happened:I Got a full database back up.I dropped the database.I created a new one with 1GB mdf and 250MB ldf.I vanished the log back -upI restored the mdf file on the new databaseThe log file again had the same huge size after the restore.How did this happen?
SQL Server overwrites your existing MDF and LDF files during the RESTORE, so it is pointless to create the database first. Whatever the sizes of the source files in the backup were, that's what size they will be after the RESTORE.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-06 : 17:56:13
|
| shrink your LDF prior to the backup and you can avoid the problem.-ec |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-06 : 22:28:47
|
| Or shrink log file on target server after restoring. |
|
|
|
|
|
|
Strange Things