Data Partitioning - Pro SQL Server Internals - page 324

Listing 15-24 shows the code for moving data files from filegroup FG2013 to disk S: . It assumes that the filegroup
has two files with the logical names Orders2013_01 and Orders2013_02 before the execution.

Listing 15-24. Moving data files between disk arrays
use master
go

alter database OrderEntryDB
add file
(
name = N'Orders2013_03',
filename = N'S:\Orders2013_03.ndf'
)
to filegroup [FG1];

alter database OrderEntryDB
add file
(
name = N'Orders2013_04',
filename = N'S:\Orders2013_04.ndf'
)
to filegroup [FG1]
go

use OrderEntryDb
go

-- Step 1: Shrinking and removing first old file
dbcc shrinkfile(Orders2013_01, emptyfile);
alter database OrderEntryDb remove file Orders2013_01
go

-- Step 2: Shrinking and removing second old file
dbcc shrinkfile(Orders2013_02, emptyfile);
alter database OrderEntryDb remove file Orders2013_02

■ Make sure to create new files with the same initial size and auto growth parameters, with growth size
specified in MB. this helps SQL Server evenly distribute data across data files.

Important

There are two caveats with such an approach. When you empty a file with the DBCC SHRINKFILE command, it
distributes the data across all other files in the filegroup including files that you will empty and remove in the next
steps, which adds unnecessary overhead to the system.
I will try to illustrate it with a set of diagrams. Figure 15-11 shows the original data placement. I am using different
shading to distinguish between data from the different files.