May 21, 2012 at 1:18 pm
Does the database with huge ldf file and smaller mdf file takes long time to restore compared with the database with huge mdf file and smaller ldf file?
Thanks
May 21, 2012 at 2:04 pm
I'm not 100% certain but I believe its going to be pretty much the same either way. The restore has to write both files so I imagine that if you have a mdf of 1gb and a ldf of 10mg it will restore at the same speed as a DB with a mdf of 10mg and a ldf of 1gb. The big thing is going to be how much of the ldf and mdf are actually in use. If your ldf is 1gb but only 100mg are in use that will be different than a ldf that is 1gb and completely full. Same with mdf of course.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
May 21, 2012 at 2:20 pm
i think it has to do with applying the logs prior to teh database being marked as ready to use.
that for a big MDF/Small LDF, the restore just has to find space for the data,and then apply the small number of changes in the LDF.
The opposite, where it has to find that same space,and then apply all the changes in the log, one after the other, until it reaches the end of the log before it can mark the database Ready, takes longer.
Lowell
May 21, 2012 at 3:24 pm
the real answer to this is probably 'it depends' but I would expect a huge .ldf is more likely to have a greater adverse affect on restore times.
Restore time would be affected by whether the database files already existed, whether instant file initialisation was in use which would speed up the data file restore but not the .ldf file, a huge .ldf would likely have a large number of vlfs which slows recovery times appreciably and another consideration would be how much active data there was in the .ldf.
---------------------------------------------------------------------
May 21, 2012 at 4:06 pm
There are two aspects to this:
- Size of the log file
- Size of the active portion of the log
If the log file does not exist (so restoring to a new database) the log file has to be created and zero-initialised. That takes time
At the end of the restore, the portion of the log that was active at the time the backup completed must be processed to run crash-recovery. If the active portion of the log is large or there are a lot of VLFs (thousands) then this crash-recovery can take a lot of time.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply