September 3, 2012 at 7:22 pm
I have a SQL 2005 production database running with a Full recovery model. Once a week I have a SP script that restores a copy of that database to a test database (Sandbox) which has a Simple recovery model.
RESTORE DATABASE [Sandbox]
FROM DISK = @RestoreFile
WITH FILE = 1,
MOVE N'Production_Data' TO N'D:\SQLLog\Sandbox.mdf',
MOVE N'Production_Log' TO N'E:\SQLLog\Sandbox_log.ldf',
NOUNLOAD, REPLACE
Are there any shortcuts that I can use since I do not need the full model? I suppose I can add the following code:
ALTER DATABASE Sandbox SET RECOVERY SIMPLE
DBCC SHRINKDATABASE(N'Sandbox')
but it seems like a waste. Any thoughts?
September 3, 2012 at 7:44 pm
When you restore a database, the restored copy will be in the same recovery model as the source database was when backed up.
If you want the restored copy to be in a different recovery model, you should run the
ALTER DATABASE [Sandbox] SET RECOVERY SIMPLE;
command as you are already doing. I can't think of any better way to do this.
Are you sure you need to run DBCC SHRINKDATABASE? I would avoid that at all costs - it will kill your performance on that restored copy.
September 4, 2012 at 4:55 am
try this instead of complete db if you want to clear the only the logs.
dump tran dbNamewith no_log
dbcc shrinkfile (dbName_logName,truncateonly)
do you have any space issue to clear the log if it is not then leave it as the simple recocery will take care and avoild further log growth.
Regards
Durai Nagarajan
September 4, 2012 at 5:15 am
The truncate only option is only valid for data files, it's ignored completely when shrinking a log file.
Dump tran has been deprecated for a long time and should not be used, neither should the no_log option, and it's not necessary anyway if the DB was just switched to simple recovery model.
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
September 4, 2012 at 5:24 am
thanks gail
i never been updated with that.
Regards
Durai Nagarajan
September 4, 2012 at 10:54 am
Great feedback. Thank you everyone.
I do not have a space issue. I simply wanted to reduce the log file space used because the server is virtualized and there are nightly VM backups of all of the drives. And no, I dont want to get into a debate on VM backups - we handle VM and SQL backups differently. My thought was that there is no sense in backing up a dead logfile.
It is a test database so performance is not a significant concern - at most there are 5 users accessing it. If there is a performance problem I will remove the shrink.
September 4, 2012 at 11:08 am
If you want to reduce the log size, just use ShrinkFile and shrink the log. Shrinking the data file does require log space, so if a little counter to your intended goal.
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
September 6, 2012 at 2:09 pm
As others have said, don't shrink files unless you really have no choice. I know this is development, but once you shrink the files, your indexes will generally get much more fragmented than they were before, and then--unless the developers aren't fussy about performance in dev environment or database is extremely small--you'd want to do a reorg or perhaps rebuild job for your indexes.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply