January 26, 2009 at 10:24 am
Hi all,
In our organization we use a production copy environment containing 2 SQL 2005 instances. Main purpose is generating statistics and querying data without affecting the load on production.
Other relevant information
- Every night we restore production database backups to the statistics instances using a SQL agent job
- All production database backups use the FULL recovery model, but for statistics we use for all databases the SIMPLE recovery model
The problem
Transaction Log files are very large after restoring the database.
We also need to put the recovery model back to SIMPLE after the restore because the backups were made using the FULL model. That's why we switch the DB back to SIMPLE using an ALTER DATABASE command.
It seems that when restoring the database log files it will also reserve the same amount of LOG space as used in production environment. We use a SIMPLE model we don't need a big log file.
This is the reason why we execute a SHRINKDATABASE command after the restore.
My Question
Is it possible to control the size of the TRANSACTION LOG file in the RESTORE command? The restore command takes a lot of time and we don't need all the LOG space.
Best regards, Peter
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
January 26, 2009 at 10:33 am
When you restore a backup, all database files will be created at the same size the database was when you backed it up.
If you want to control the transaction log size, you will have to do it in the production environment.
January 26, 2009 at 1:09 pm
if its only the log you want to shrink use the shrinkfile command instead. for just the log file.
s this statistics database updated? if so even in simple mode it will still write to the log so if you have the space might as well leave it as it is.
---------------------------------------------------------------------
January 26, 2009 at 1:23 pm
Michael Valentine Jones (1/26/2009)
When you restore a backup, all database files will be created at the same size the database was when you backed it up.If you want to control the transaction log size, you will have to do it in the production environment.
Yes that was also my thought, but I wanted to be sure.
I think we still can do some tuning as we take LOG backups every hour and our log files are sometimes still bigger than 10 GB and only use 10 percent of the space.
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply