How to Merge SQL Server Data Files

I ran DBCC SHRINKFILE with EMPTYFILE against a 400GB database in FULL recovery and watched the transaction log go from about 20GB to nearly 200GB before I killed it. The operation is fully logged, one page at a time, and in FULL recovery every one of those page moves is sitting in the log waiting on a log backup. That’s the thing I’d want to know before starting, so it’s going first.

The database that got me here had one .mdf and seven .ndf files. That’s not unusual in older systems and it’s usually not necessary now. The workload wasn’t pushing any storage limits and the disk was modern enough to handle a single data file, so the plan was to restore it as-is and then consolidate.

Multiple data files used to spread I/O across spinning disks, which was a real benefit at the time. On SSDs and SANs that advantage is typically small or gone, and managing the extra files costs more than it returns.

Here’s the approach I used, along with a reproducible lab environment for testing.

Why This Database Had Eight Files

SQL Server stores data inside filegroups, which are made up of logical files that point to the actual .mdf or .ndf files on disk. When you restore a backup, SQL Server expects every file in that backup to exist. There’s no way to merge files during the restore itself. You have to bring the database online first, then consolidate afterward.

I prefer to collapse files when they’re just leftover structure from an older environment:

• You’re no longer on spinning rust.
• There’s no real multi-volume layout behind them anymore.
• The additional files create unnecessary complexity for backup operations, monitoring, and restore procedures.

Multiple data files still make sense in some scenarios (TempDB, multiple storage tiers, massive OLTP), but in a lot of line-of-business systems they’re just historical clutter.

Demo Setup: Build a Playground With Multiple Data Files

Here’s a self-contained lab script you can run on a dev instance. It creates a database with one MDF and three NDFs, loads some data, and gives you something to merge.

USE master;
GO

IF DB_ID('DemoMergeFiles') IS NOT NULL
BEGIN
    ALTER DATABASE DemoMergeFiles SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DemoMergeFiles;
END
GO

CREATE DATABASE DemoMergeFiles
ON PRIMARY
(
    NAME = N'DemoMergeFiles_Primary',
    FILENAME = N'F:\SQLData\DemoMergeFiles_Primary.mdf',
    SIZE = 200MB,
    FILEGROWTH = 50MB
),
(
    NAME = N'DemoMergeFiles_NDF1',
    FILENAME = N'F:\SQLData\DemoMergeFiles_NDF1.ndf',
    SIZE = 200MB,
    FILEGROWTH = 50MB
),
(
    NAME = N'DemoMergeFiles_NDF2',
    FILENAME = N'F:\SQLData\DemoMergeFiles_NDF2.ndf',
    SIZE = 200MB,
    FILEGROWTH = 50MB
),
(
    NAME = N'DemoMergeFiles_NDF3',
    FILENAME = N'F:\SQLData\DemoMergeFiles_NDF3.ndf',
    SIZE = 200MB,
    FILEGROWTH = 50MB
)
LOG ON
(
    NAME = N'DemoMergeFiles_Log',
    FILENAME = N'F:\SQLData\DemoMergeFiles_Log.ldf',
    SIZE = 200MB,
    FILEGROWTH = 50MB
);
GO

ALTER DATABASE DemoMergeFiles SET RECOVERY SIMPLE;
GO

Adjust paths to match wherever your instance keeps data and log files.

Load Some Data So The Files Actually Get Used

Now we populate data to ensure files are utilized. This table is designed with a wide structure to distribute pages across the filegroup.

USE DemoMergeFiles;
GO

IF OBJECT_ID('dbo.BigDemo', 'U') IS NOT NULL
    DROP TABLE dbo.BigDemo;
GO

CREATE TABLE dbo.BigDemo
(
    Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    SomeText CHAR(4000) NOT NULL,
    SomeNumber INT NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);
GO

-- Adjust this to make EMPTYFILE take longer or shorter.
DECLARE @TargetRows INT = 500000;

WITH Numbers AS
(
    SELECT TOP (@TargetRows)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b
)
INSERT dbo.BigDemo (SomeText, SomeNumber, CreatedAt)
SELECT REPLICATE('X', 4000),
       n % 1000,
       DATEADD(SECOND, n, '2025-01-01')
FROM Numbers;
GO

On my test system, half a million rows provides sufficient data distribution across all files without requiring excessive processing time.

Determine File Usage Patterns

After the restore, or the demo setup above, the first thing I look at is the file layout and how much space each file is actually using.

USE DemoMergeFiles;
GO

SELECT  name,
        type_desc,
        physical_name,
        size * 8 / 1024 AS SizeMB,
        FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS UsedMB
FROM sys.database_files;

Pre-Migration Safety Preparations

In production I do a few things before touching the file structure:

• Ensure recent backups are available and restorable.
• Transition to SIMPLE recovery model if appropriate.
• Schedule operations during periods of minimal I/O impact.

In this demo we already set the database to SIMPLE. If you’re starting from a restore in FULL, this is roughly what I’d do:

ALTER DATABASE DemoMergeFiles SET RECOVERY SIMPLE;
GO

I also tell people it’s going to be resource-intensive and fully logged, even in SIMPLE. The database stays online the whole time, which sounds better than it is, since EMPTYFILE takes a while and it’s competing with everything else for I/O.

Move Pages With EMPTYFILE

To collapse files within a filegroup, you move all the pages out of the files you’re removing and into the ones you’re keeping.

The core commands for a single file look like this:

USE DemoMergeFiles;
GO

DBCC SHRINKFILE (N'DemoMergeFiles_NDF3', EMPTYFILE);
GO

The subsequent cleanup command is:

ALTER DATABASE DemoMergeFiles
REMOVE FILE DemoMergeFiles_NDF3;
GO

Behind the scenes, EMPTYFILE walks the file page-by-page, moving allocations into other files in the same filegroup. The documentation says it “migrates all data from the specified file to other files in the same filegroup,” and that filegroup restriction is why this collapses files inside a filegroup and can’t be used to move data between them. It’s fully logged, and it’s single-threaded as far as I can tell from watching it, though that second part is my observation rather than something I found written down.

I went looking at that page again while writing this up and found something that would have changed how I handled the 400GB attempt: “If you use the EMPTYFILE parameter and cancel the operation, the file isn’t marked to prevent additional data from being added.” That marking is what keeps new data out of a file you’re emptying, so cancelling the command gives up more than the remaining work. The pages that already moved stay moved, but the file accepts writes again, and I’d assumed the one I killed was sitting there empty and inert until I came back to it.

Monitoring Data Page Migration Progress

EMPTYFILE operates slowly and provides minimal progress feedback in the Messages tab. To monitor operation status, I query sys.dm_exec_requests.

SELECT  session_id,
        command,
        status,
        percent_complete,
        start_time,
        total_elapsed_time,
        wait_type,
        wait_time,
        last_wait_type,
        cpu_time,
        reads,
        writes
FROM sys.dm_exec_requests
WHERE command LIKE '%DBCC%';

In the demo environment, this operation may complete quickly depending on row count. For extended testing, increase @TargetRows in the initial data load to provide more work for EMPTYFILE.

In one production scenario, a ~220 GB NDF required approximately three hours to complete migration. While this operation runs online, I recommend scheduling during maintenance windows to minimize performance impact.

Remove The Emptied File

Following successful EMPTYFILE completion, the target file contains no allocated pages and can be removed from the database. After file removal, verify the updated layout:

SELECT  name,
        type_desc,
        physical_name,
        size * 8 / 1024 AS SizeMB,
        FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS UsedMB
FROM sys.database_files;

In production environments, repeat the “EMPTYFILE + REMOVE FILE” sequence for each secondary file scheduled for removal.

Post-Migration Validation

Following file consolidation, I perform comprehensive validation:

1. Check the file layout again.
2. Run DBCC CHECKDB.
3. Take a fresh full backup if you have time.
4. Switch recovery model back to whatever it should be.

-- Sanity check
DBCC CHECKDB (N'DemoMergeFiles') WITH NO_INFOMSGS;
GO

-- Reset database back to FULL
ALTER DATABASE DemoMergeFiles SET RECOVERY FULL;
GO

Production Environment Considerations

• Pre-growing the destination files doesn’t make EMPTYFILE any faster. It avoids autogrowth events during the move, which is worth doing, but the page migration is still single-threaded and still fully logged. I expected this to help more than it did.
• FULL recovery is what caused the log growth I opened with. If you can’t switch to SIMPLE, frequent log backups during the operation are the alternative, and you need somewhere to put them.
• I’ve never measured a performance improvement from consolidating files. What you get is one file to think about instead of eight during a restore, and that’s the whole return.

When Multiple Data Files Are Still Worth It

To clarify, this approach is not intended for universal .ndf removal:

• TempDB absolutely benefits from multiple files in many environments.
• Very large databases spanning multiple volumes might still need multiple files per filegroup.
• Partition schemes and archival strategies sometimes lean on separate files for a reason.

It takes a long time, and you only pay it once. What you’re left with is one data file instead of eight, which matters most on the day someone has to restore it somewhere else.

None of this makes the server faster tomorrow. What I still don’t know is where the line is, since I’ve done this on a 400GB database and abandoned it once already, and I don’t have a good rule for how large is too large to attempt in a single maintenance window. If you want to try it or show someone, the DemoMergeFiles database above runs the whole thing end to end without touching anything real.