August 30, 2012 at 10:59 pm
I'm trying to restore the DB which was taken from Production environment and it requires more than 8GB. After restoring DB found that LOG file alone occupies more that 4GB. Is log file required when restoring DB into new environment? If not, can we restore DB without restoring LOG file?
Backup file size (.BAK): 4.10 GB
After restoring .MDF file size: 4.10 GB
After restoring .LDF file size: 8.82 GB
As said in above metrics, LOG file along it takes near 9GB...this needs to be avoided..
Please let me know what can I do?
Thanks.
Whizkid
August 30, 2012 at 11:29 pm
The log file is not something like an errorlog that you can get rid of. Logfile is a part of the database.
You can probably restore the database and truncate the log and shrink the log file. (but be careful, truncating log will effect in not being able to restore to point in time and shrink is generally not a good practice)
August 30, 2012 at 11:34 pm
whizkidgps (8/30/2012)
... can we restore DB without restoring LOG file?
No. DB cannot be restored without LOG file.
Restored database would be just like the backedup DB. You can only move the files to different drive or path.
August 30, 2012 at 11:35 pm
Do you want me to truncate the LOG file after restoring DB into new environment? or Can we take the DB backup after truncating LOG file from Source DB server?
August 30, 2012 at 11:42 pm
If I were you, I would never truncate transaction log on a production database.
What are you trying to achieve by removing the extra 4 GB? space issues? Can you not take a backup and compress it with 7zip (if you dont use native compression), move the file to the other server, restore and then truncate log?
August 30, 2012 at 11:46 pm
Yes, Space issues..I've moved the .BAK file to other server after compressing as ZIP file. Please find below metrics
Backup file size (.BAK) : 4.10 GB (after unzip)
After restoring .MDF file size: 4.10 GB
After restoring .LDF file size: 8.82 GB
As said in above metrics, LOG file along it takes near 9GB and total DB size is near 13 GB.
Can't we do anything for using optimized space?
August 30, 2012 at 11:47 pm
The Log file is basically maintaining the logs of transactions and it must be included as it helps to view the previous level, but you can truncate it if you have no further use.
Also as you are restoring it at another place so there is no need to truncate the log files of the production
Thanks,
Sumit
August 30, 2012 at 11:51 pm
whizkidgps (8/30/2012)
Do you want me to truncate the LOG file after restoring DB into new environment?
Yes.
or Can we take the DB backup after truncating LOG file from Source DB server?
No.
Your TLog file is bigger than data file. Looks like you are not taking tlog backup regularly.
August 30, 2012 at 11:54 pm
If I truncate the LOG file, will it cause delete data from any of the table? What would be the problem if I truncate the LOG file?
August 31, 2012 at 12:13 am
whizkidgps (8/30/2012)
If I truncate the LOG file, will it cause delete data from any of the table?
No
What would be the problem if I truncate the LOG file?
If you shrink it once off, nothing.
If you truncate it (which does not mean shrink for a log file) you could be messing up your backup strategy.
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2012 at 12:27 am
How would I do the truncate of log whole file?
August 31, 2012 at 2:39 am
You're not trying to truncate it, you're trying to shrink it. Huge difference.
Read up on DBCC ShrinkFile, you want to shrink the log only and shrink it to a reasonable size, NOT 0. With a data file of 4GB, guess would be about 3GB for the log would be reasonable.
p.s. Please read that article...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply