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

By:   |   Updated: 2016-08-04   |   Comments (3)   |   Related: More > Database Administration


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.

Solution

I described how data is stored in SQL Server data files and how to migrate data from one data file to another in my last two tips. I would suggest reading both tips to get indepth knowledge of these topics. In this tip, I will describe how to remove a SQL Server data file for a multi-file database.

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.

CREATE DATABASE [DataFileRemoval]
CONTAINMENT = NONE
ON  PRIMARY
( NAME = N'DataFileRemoval', FILENAME = N'E:\MSSQL\DATA\DataFileRemoval.mdf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ),
( NAME = N'DataFileRemoval_1', FILENAME = N'E:\MSSQL\DATA\DataFileRemoval_1.ndf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ),
( NAME = N'DataFileRemoval_2', FILENAME = N'E:\MSSQL\DATA\DataFileRemoval_2.ndf' ,SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )
LOG ON
( NAME = N'DataFileRemoval_log', FILENAME = N'E:\MSSQL\DATA\DataFileRemoval_log.ldf',SIZE = 10MB , MAXSIZE = 1GB , FILEGROWTH = 10%)
GO

Next step is to create a table in which we will insert data. Run the below command to create a table.

USE Manvendra;
GO
CREATE TABLE [Test_Data] (
    [Sr.No] INT IDENTITY,
    [Date] DATETIME DEFAULT GETDATE (),
    [City] CHAR (25) DEFAULT 'Bangalore',
 [Name] CHAR (25) DEFAULT 'Manvendra Deo Singh');

Now we will insert some rows into the above created table, so data can be saved in each data file. Run the below command to insert 10,000 rows to table "Test_Data".

USE Manvendra
go
INSERT INTO Test_DATA DEFAULT VALUES ;
GO 10000

Check the row count by running the below T-SQL code to validate the data insert operation.

--Check the row count.
USE [DataFileRemoval];
GO
SELECT COUNT(*) [No. of Rows] FROM Test_DATA

Row Count of the Test_DATA table in SQL Server Management Studio

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

Number of extents allocated to each SQL Server data file

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]

SQL Server Database File Removal Failure

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 File 'FileName' cannot be removed because it is not empty. (Microsoft SQL Server, Error:5042)

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.

USE [DataFileRemoval];
go
DBCC SHRINKFILE ('DataFileRemoval_2', EMPTYFILE);

SQL Server DBCC SHRINKFILE with the EMPTYFILE option

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

Check the number of allocated pages post data migraton for the SQL Server database with 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]

Remove target SQL Server data file

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 

Check SQL Server data files post removal with 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

Check row counts post data file removal

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.

Validate SQL Server data file is removed in SQL Server Management Studio
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.
  • Explore more knowledge with these SQL Server Database Administration Tips


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2016-08-04

Comments For This Article




Monday, May 3, 2021 - 3:26:41 AM - Bui Tan Duoc Back To Top (88636)
I want to remove file with partitioned data of Jan-2017!

DBCC SHRINKFILE ('F_2017_01_01', EMPTYFILE);

Error:
There is insufficient space in the filegroup to complete the emptyfile operation.

Friday, August 5, 2016 - 8:27:15 AM - Manvendra Back To Top (43059)

 Hi Steve,

 

As per Microsoft, Primary datafile can not be removed so you can not remove it.


Thursday, August 4, 2016 - 7:28:17 AM - Steve Johnson Back To Top (43052)

 What if the file you want to remove is the .mdf ? I receive an error saying the primary data faile cannot be removed.

 















get free sql tips
agree to terms