SQL Server databases store their data and objects in files. Each database has at least one data file (and a log file), but it’s not restricted to just one – it could have many data files. If you ever find yourself in the situation where you need to remove a data file from a database, you’ll need to empty that file first, before removing it.

But don’t worry, emptying a file doesn’t actually delete the data. It simply migrates the file’s data to other files within the same filegroup.

The examples below demonstrate how to empty a data file, then remove it from the database using Transact-SQL.

Quick Example

Here’s a quick example to show you how to empty a data file and remove it from the database:

-- Empty the file
DBCC SHRINKFILE (Solutions2, EMPTYFILE);  
GO 

-- Remove the file
ALTER DATABASE Solutions  
REMOVE FILE Solutions2;  
GO

So as explained, this empties the file, then removes it completely from the database. When you use EMPTYFILE, this moves all data from the file to other files in the same filegroup. Therefore, you’ll need to make sure this isn’t the only file in the filegroup (otherwise you’ll get an error). EmptyFile also assures you that no new data will be added to the file.

A Longer Example

In case you’re confused by the previous example, let’s walk through the process of creating a new database, adding a new data file, then emptying it, then removing it.

Create a Database and View its Data File Information

-- Switch to the master database
USE master;
GO

-- Create a new database
CREATE DATABASE Test;
GO

-- View it's data file and log file info
USE Test;  
GO  
SELECT file_id, name, type_desc, physical_name, size, max_size  
FROM sys.database_files ;  
GO

First we switch to the master database before creating a new database called Test.  We then switch to the new database and select certain info about its database files from the sys.database_files system catalog view.

Add a New Data File

-- Add a new data file
ALTER DATABASE Test   
ADD FILE (  
    NAME = Test2,  
    FILENAME = '/var/opt/mssql/data/Test2.mdf',  
    SIZE = 8MB
    );  
GO

-- View it's data file and log file info
USE Test;  
GO  
SELECT file_id, name, type_desc, physical_name, size, max_size  
FROM sys.database_files ;  
GO

We now add a new data file with a logical name of Test2 and a physical path of /var/opt/mssql/data/Test2.mdf (note that this is a Linux/Mac path. If using Windows, use backslashes instead of forward slashes). Again we query the sys.database_files system catalog view so that we can see the details of our new file.

Empty the Data File and Remove it

For the purposes of this example, let’s assume that the database has been put into production and the data file we just created has been loaded up with data. Now we want to remove that file (for whatever reason). But before we remove the file, we need to empty it (migrate its data to another file).

Here’s how to do that:

-- Empty the new data file
DBCC SHRINKFILE (Test2, EMPTYFILE);  
GO

-- Remove the file
ALTER DATABASE Test  
REMOVE FILE Test2;  
GO 

-- View it's data file and log file info
USE Test;  
GO  
SELECT file_id, name, type_desc, physical_name, size, max_size  
FROM sys.database_files ;  
GO

So this is just like the first example on this page, except our database has a different name. And in this example, we query sys.database_files to confirm that the file has indeed been deleted.