Redistribute data evenly across equally sized data files

· Uncategorized
Authors

Suppose your database files look something like this:

File size in MB used in MB
mdf

12,503

12,462

2.ndf

62

62

3.ndf

1,997

758

And you want all data to become evenly distributed over all files with same size so future load will be equally distributed over all files.

There are various ways you can follow to accomplish this:

  1. By exporting all data and import them into a new db with correct file configuration.
  2. Use (clustered) index rebuild. But both can become rather complex if you have to transfer hundreds of tables and TB’s of data.
  3. Using the EMPTYFILE option when shrinking a database file.

This blogpost describes the second method and how to proceed taking the results of my tests into account.

Let’s start with the file configuration as mentioned earlier.

Step 1: Create a set of new ndf files. The number of new files should correspond to the number you want the DB to use apart from the mdf file at the end. I particularly want the mdf file to contain only system objects and all user objects are spread over 4 files of 4GB each.

File size in MB used in MB
mdf

12,503

12,462

2.ndf

62

62

3.ndf

1,997

758

N1.ndf

4,096

0

N2.ndf

4,096

0

N3.ndf

4,096

0

N4.ndf

4,096

0

Step 2: shrink all old ndf files and disable auto growth. Redistribution afterwards will not copy data in these files unnecessarily.

Step 3: Shrink the smallest old ndf file (2.ndf) using the EMPTYFILE option.

File size in MB used in MB
mdf

12,503

12,462

2.ndf

62

0

3.ndf

1,997

758

N1.ndf

4,096

15

N2.ndf

4,096

15

N3.ndf

4,096

15

N4.ndf

4,096

15

Step 4: delete 2.ndf

Step 5: GOTO step 3 until all old ndf files are gone

This is the results if file 3 was deleted:

File size in MB used in MB
mdf

12,503

12,462

N1.ndf

4,096

198

N2.ndf

4,096

198

N3.ndf

4,096

198

N4.ndf

4,096

226

Step 6: Shrink mdf files using the EMPTYFILE option. This process might take some time. Be patient, it’s worth to wait for. This will generate an error because he can’t move system objects. You can ignore this because all user objects have been moved.

File size in MB used in MB
mdf

12,503

0

N1.ndf

4,096

3,318

N2.ndf

4,096

3,318

N3.ndf

4,096

3,318

N4.ndf

4,096

3,338

Step 7: shrink mdf file and disable auto growth. So only ndf files will be used to store user objects.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: