Post reply

How to divide single large mdf file into multiple small mdf files

  • December 1, 2011 at 5:26 am

    #260616

    Hi;

    I have a large data base having mdf file size as 150GB, it is having only 1 datafile (mdf file).

    My target is to add 2 more datafiles(mdf) in this database, and also distribure data across these 3 data file. my doing this we will have 1 data base with 3 data files and having approx 50 GB data on each data file.

    Can you please help how can I acheive this target.

  • December 1, 2011 at 6:12 am

    #1415212

    SQL Server should stripe data between datafiles automatically. I think that rebuilding all clustered indexes after adding the new files should do the trick.

    However, I would suggest to identify objects that belong to the same subject area and store them in different filegroups. This will make your life easier.

    -- Gianluca Sartori

  • December 1, 2011 at 10:12 am

    #1415405

    What is the reason for splitting the file? Are you spreading the data across multiple disks?

    There are several ways to spread things around. First, please realize that you can't force data into a specific file; sql server will put it where it has the most free space % (proportional fill). You only get to choose the file group and the size of the files. I'm going to assume the the 150GB file is 90% full (135GB)

    approach 1 (only online operations, so no blocking):

    Add 3 new files @ 50GB. (yes, that means 4 total)

    run DBCC SHRINKFILE on file1 with the emptyfile flag. This will take a really, really, really long time, and will cause massive amounts of fragmentation in the database. when it finishes, you can set the size of file1 to something tiny( 10 MB). Reorg all of the indexes. done.

    approach 2:

    add 3 new files @ 50GB to a new file group [SECONDARY]. Rebuild all indexes onto the new filegroup. This is much faster than option 1, but will cause blocking and requires altering the definition of every index/table in the DB. When it's finished, you can shink the orig file in PRIMARY.

    approach 3:

    same as approach 2, except you create 150GB file on SECONDARY, rebuild everything to it. when it finishes, you shrink file1 to 50GB, add 2 more 50GB files to PRIMARY, then rebuild all the indexes back to the original file group. when done, you can drop SECONDARY file group.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply