During DB Restoration in SQL, SQL Log increased unexpected

roland

During restoring Navision backup into the SQL Database, happened that the SQL Tranasaction log size increased unexpected.

The backup file contain one company book only. There are two files created from Navision backup, and the size is 2,097,152KB and 1,836,799KB.

When the SQL Data file size reach 15GB, the transaction log file size increased to 35GB.

I restore in a new database with giving size of 10GB for Data file and 2GB for Log file with unrestricted growth.

What is the reason that could cause the SQL transaction log file can growth so fast?

If anyone of you have answer please let me know. I'm looking for solution for this urgently.

Thank You

kine

When are you using Navision backup for restoring, navision is inserting data into database - each insert is writen into transaction log -> log have same or greater size than db. When you restoring, switch recovery model of yourt db to Simple (no tr. log is created) and after restoring switch it back to full. Because in tr. log are all activities on DB, tr. log size > DB when you are restoring. 8)

roland

Hi kine

Thanks for your reply. I had did that but it still growth up to 35GB for the Transaction log file (Tr.log) and restoration interrupted due to not enough space for the log growth.

My database most properly is about 12GB.

Is that tr.log growth sound healthy.

Other than getting a bigger hardisk can there be any other ways to do it?

kine

How big do you have tr.log before restoring backup?

rsaritzky

If I read your post correctly, You already have a good backup of the database. If so, you can reduce the size of your log file now, before you do your restore.

1. Change the recovery model to "Simple", as previously recommended.
2. In Enterprise Manager, click "All Tasks", then click "Shrink Database".
3. On the next window, click "files". Then, choose the log file using the pulldown. Click OK. Your log file should shrink down to a relatively small size (30 mb or less).
4. Do your restore.
5. Then you can potentially change your recovery model back to "full", but you should be backing up the log file and resetting it every time you do a Navision backup. If you don't do this, then leave the recovery model as "simple" - it's very unlikely that you would choose to do a SQL roll-forward on a database restore unless you follow the other rules (like backing up the log file upon every backup).

Regards,

roland

I had did that to reduce my log file size. Before my backup the log file only have less than 100MB.

But when I restore it growth so huge unbelievable.

GoMaD

May sound stupid but have you tried a stop and start of your SQL server prior to the restore and after the changing of the Recovery Model?