August 14, 2013 at 10:43 am
Hey guys,
At my organization, we have just built a new SQL Server machine to which we would like to migrate existing databases from other servers. For one of the databases, we'd like the MDF and LDF to have a new initial file size once it is moved. What is the best way to accomplish this? Is there a way to increase the file sizes during restoration from a backup? Or will we have to restore and than alter the file size through T-SQL (or SSMS)?
The reason we are asking, is because we want to ensure that the MDF and LDF files on the new server are contiguous in the file system, and we are worried that increasing the file size after restoration will lead to having non-contiguous files in the file system.
If there is more detail I can provide, just let me know.
Thanks,
August 14, 2013 at 12:01 pm
A restore will recreate the database as it was at the time of backup. If you want something different, do the restore then make the changes.
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 14, 2013 at 12:15 pm
GilaMonster (8/14/2013)
A restore will recreate the database as it was at the time of backup. If you want something different, do the restore then make the changes.
Well, if we increase the MDF and LDF file sizes after the restore, will the files be contiguous on the file system?
August 14, 2013 at 12:31 pm
Maybe, maybe not. Depends on way too many factors to answer.
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 14, 2013 at 1:25 pm
GilaMonster (8/14/2013)
Maybe, maybe not. Depends on way too many factors to answer.
Ah, okay, I was afraid so. How does this sound then?
1) Restore the Database on the new machine
2) Increase the filesize
3) Backup the new database
4) Restore the backup of the increased database
In your opinion would that help ensure that the database file's are contiguous?
August 14, 2013 at 2:10 pm
Maybe. Why's it so essential that the files be contiguous?
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 14, 2013 at 2:17 pm
GilaMonster (8/14/2013)
Maybe. Why's it so essential that the files be contiguous?
Being a brand new server, we'd like to start it off on the right foot. Based on this MSDN article:
http://technet.microsoft.com/en-us/library/ms188917.aspx
Extent Fragmentation
This is the percentage of out-of-order extents in the leaf pages of a heap. An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.
We'd like to start with extent fragmentation as close to zero as possible.
August 14, 2013 at 2:48 pm
Extent fragmentation has to do with the position of extents within the file, not the file in the storage system. No amount of backup/restore will change the position of extents within a data file.
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 14, 2013 at 3:34 pm
dgutie02 (8/14/2013)
Ah, okay, I was afraid so. How does this sound then?
1) Restore the Database on the new machine
2) Increase the filesize
3) Backup the new database
4) Restore the backup of the increased database
I am not sure that this will help. It is not unlikely that you will get back the same disk blocks.
You could extend the files on the old server before you backup.
Else, stop SQL Server and let loose the disk fragementer of your choice.
And I agree with you: database files should of course of be contiguous on disk.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 14, 2013 at 6:59 pm
If you buy the right disk defragmentation system, you can defragment SQL Server MDF, LDF, and NDF files while the system is up and running with little or no degredation in performance while it's defragging. Here's one that I used many years ago (disclaimer... I've not tested this particular version). It was a beautiful thing because once I set it up, I never had to touch it again.
http://www.condusiv.com/solutions/server-solutions/sql-server/default.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2013 at 2:34 am
Jeff Moden (8/14/2013)
If you buy the right disk defragmentation system, you can defragment SQL Server MDF, LDF, and NDF files while the system is up and running with little or no degredation in performance while it's defragging.
You can, but to be honest I'd usually recommend that SQL be shut down for a disk defrag, as if there's any problems with that online defrag tool, the DB could end up corrupt or worse.
Also worth noting that SANs usually have their own tools for defragging, SSDs probably shouldn't be defragged at all (check the specific SSD's manual and guidelines) and some SAN designs intentionally fragment files in order to spread the IO out across as many disks as possible to improve read throughput.
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy