How to remove a SQL Server Data file for a multi-data file database

Problem

You have a SQL Server database with multiple data files and you want to remove one of
the data files. This tip will demonstrate the method to remove a data file
from a SQL Server database without losing data.

Let's start with creating a database that has three data files with one filegroup
and a log file and insert some data into the database.
Finally we will remove one data file to show you the step by step method.

Create a Sample SQL Server Database

First we will create a database named "DataFileRemoval" with three
data files (1 primary and 2 secondary data files) in one filegroup and one log
file by running the below T-SQL code. You can change the name of the database, file path,
file names, size and file growth according to your needs.

Now we will check the number of pages/extents allocated from each
of the data files. Here we can also validate that data has been stored on each data file
by looking at the used extents.

DBCC showfilestats

The primary data (filedid=1) file has used 42 extents and the secondary data files
(fileid=3 and fileid=4) have used 13 extents (6 and 7).

Removing a SQL Server Data File Fails

Now we will remove one of the SQL Server data files by running the below T-SQL
code.

ALTER DATABASE [DataFileRemoval] REMOVE FILE [DataFileRemoval_2]

We can also try to remove data file in SQL Server Management Studio as shown in
the below screenshot. Right click on the database, choose Properties
and a database property window will open. Choose Files from the left side pane, click
on the file you want to remove from the right side pane and click the Remove
button at the bottom of the window.

The error states that the file needs to be empty. To do this, we will move data from this data file to
the other data files
and then we can try to remove this data file. To achieve this, we will use
DBCC SHRINKFILE with the EMPTYFILE option to move data between files as we have done
in our
last tip.

Moving SQL Server Data between Data Files

As you know, data is stored on all data files, so as per the requirement we have to
migrate data from one data file to the others before we can remove this data file. We will use
the DBCC SHRINKFILE command to migrate the data. DBCC SHRINKFILE command shrinks the
size of the specified data or log file for the current database, or empties a file
by moving the data from the specified file to other files in the same filegroup.
We need to pass the logical file name of data file from which we want to migrate our
data along with the argument "EMPTYFILE" which will empty the given file and migrate
all data from the specified file to other files in the same filegroup. Run the below
command to get this done.

We can see the "UsedPages" value shows zero which means all the data pages
allocated to this data file have been migrated to the other data files.

Now we will validate whether the data is really migrated to the other data files. We will check the allocated pages/extents of each data files by running
the below DBCC command.

DBCC showfilestats

We can see only one extent is allocated to DataFileRemoval_2 data file where earlier
it was 7. This means the rest of all extents from this file have been migrated to
the other two data files, so
their allocated extents have increased. The one extent remaining in this data
file contains the data file header information.

SQL Server Data File Removal Post Data Migration

Now that we have migrated and validated the data migration, we should be able
to remove data file datafileremoval_2. This can be removed easily either using
the GUI
or by running the ALTER statement which we ran in the sections above.

ALTER DATABASE [DataFileRemoval] REMOVE FILE [DataFileRemoval_2]

We can see data file has been removed, this also validates that data movement
was done successfully otherwise this operation would have failed. Now we can run
the below command to check the available data files in
the database.

DBCC showfilestats

You can see [DataFileRemoval_2] data file no longer exists in the database and database
has only two data files. We can also check the total row count of the table in which
we inserted 10,000 rows to make sure we still have all of the data.

USE [DataFileRemoval];
GO
SELECT COUNT(*) [No. of Rows] FROM DATA

We can see table test_data still has the same number of rows with just the two data files post
removal of data file datafileremoval_2.

We can also validate the data file removal
by looking at the database property page in SSMS. Right click on the database, click on
Properties and select Files from the left side pane.

Next Steps

Do not remove any data file of your production database until it is required.

First test this process in to lower life cycle environments and then replicate the change in
to production post approval process.