Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Yogi Char
Starting Member
4 Posts |
 Posted - 2008-06-24 : 02:21:46
|
| I had a query regarding splitting MDF files. As one of our databases has an mdf sized 75GB, I was planning to split it into 2 or 3 files and put them across different RAID disks. But I could not find a thorough source for information as to how to go about splitting an existing MDF file into smaller files. Can anybody help? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-24 : 02:27:48
|
| I think what you want is a way to shrink the MDF file. Refer this:-http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36201 |
 |
|
|
contrari4n
Starting Member
27 Posts |
Posted - 2008-06-24 : 08:03:42
|
| OK, assuming I have understood your question correctly.The following suggestion is based on these assumptions:1) You want to add 1 additional file, and have the data distributed evenly between the 2 files2) You have one filegroup, and no plans to modify the schema to introduce new ones (this would actually give you better performance)3) You have a spare 75GB free on another diskUse ALTER DATABASE to add a second file in the same filegroup and size it to the exact same size as your existing file. Make sure they are not set to autogrow. Now leave to simmer for a few days!If you are happy for these files to remain this size there is nothing more to do. The proportional fill algorithm will ensure that over time the new file is used more so that eventually they contain the same amount of data.I have avoided suggesting 2 new files and migrating the data completely with DBCC SHRINKFILE (..., EMPTYFILE) because of the additional I/O this will cause, and the time it will take, but if you have a long enough maintenance window, or an urgent need to get this done quick, or the new files have to be smaller, you may want to consider it.Richard Fryarhttp://www.sql-server-pro.comSQL Server Articles and Tips |
|
|
|
Yogi Char
Starting Member
4 Posts |
Posted - 2008-06-24 : 08:48:17
|
| Richard,Thanks for your response.All your assumptions are in line. I didn't get the comment in 2nd assumption - 'this would actually give better performance'. Should I set both mdf and ndf (new file added) not to autogrow? This solution will leave my 75 gb file as it is and add new data to the new file. If I want my existing file to come down in size, is that possible? Can I move some of the data objects on to the new file?Yogi |
|
|
|
contrari4n
Starting Member
27 Posts |
Posted - 2008-06-24 : 09:48:45
|
| Ideally you would create a couple of new filegroups (say 'DATA_FG' and 'INDEX_FG'), create 2 or more files for each filegroup and on separate disks. The ideal number of files to create per filegroup actually depends on the number of CPUs. There is plenty of information on the Microsoft website about this.Now modify your schema as follows:Drop all clustered indexes and recreate them on the DATA_FG filegroup.Drop all non-clustered indexes and recreate them on the INDEX_FG filegroup.This will leave the PRIMARY filegroup (mdf) storing metadata only. It can now be shrunk.Your original post does not say why you want to split the file. If it's for performance reasons are you sure that I/O is the cause? Making the above changes may involve a lot of work, so be sure to investigate alternatives.Richard Fryarhttp://www.sql-server-pro.comSQL Server Articles and Tips |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-24 : 09:58:49
|
| I think you don't have to drop clustered index . If you have already created new FG in separate disk with 2 files , you can move the cluster index of large table to new FG which will infact move table as well to new FG. Moving non-clustered index doesn't move your table to New FG. |
|
|
|
|
|
|
|
|
Query regarding splitting MDF files