You have a database with one or more filegroups, and one or more of those filegroups has multiple files. You’ve decided that you don’t actually need some of those additional files and want to get rid of them. Here are some steps you can follow.

Assume there are 5 files and they are numbered 1-5. We want to get rid of 2-5 and keep just file 1.

The Plan

1. Calculate the space used by each file. You can use this query or one of your own.

   SELECT DB_NAME() AS DbName, 
   DF.name AS FileName, 
   DF.size/128.0 AS CurrentSizeMB, 
   CAST(FILEPROPERTY(DF.name, 'SpaceUsed') AS INT)/128.0 SpaceUsedMB, 
   (DF.size - CAST(FILEPROPERTY(DF.name, 'SpaceUsed') AS INT))/128.0 AS FreeSpaceMB,
   CASE WHEN growth > 0 THEN 1 ELSE 0 END IsAutoGrow,
   CASE WHEN is_percent_growth=1 THEN CAST(growth AS varchar(30)) + '%'
   ELSE CAST(growth/128 AS varchar(30)) + 'MB' END AS Growth
   FROM sys.database_files DF
   JOIN sys.filegroups FG
   ON DF.data_space_id = FG.data_space_id
   WHERE FG.name = 'ExtraFG';

2. Some form of backup.
3. Disable auto-growth on all files but number 1.
4. Grow file number 1 if needed based on the information from step 1.
5. Use DBCC SHRINKFILE TRUNCATE to remove any easy excess space for all files except number 1.

   DBCC SHRINKFILE (
   {filepath}\MultiFileGroups_ExtraFG_2.ndf, 0.01, TRUNCATEONLY)

6. Run the following for each file except number 1. Start at the bottom (number 5 in my demo) and work your way up.

   -- A.
   DBCC SHRINKFILE(
   {filepath}\MultiFileGroups_ExtraFG_2, EMPTYFILE);
   -- B.
   ALTER DATABASE 'MultiFileGroups' 
   REMOVE FILE {filepath}\MultiFileGroups_ExtraFG_2;

The explanation.

Working backwards:

• 6b removes the file, but to be removed it must be empty.
• 6a empties the file by moving the data into the remaining files.
• 5 makes sure there is as little space as possible in the extra files without spending a lot of unnecessary extra time doing a proper shrink. (TRUNCATE tells it not to move any data just clear the extra space off the end of the file.) This isn’t really necessary but could be helpful.
• 4 makes sure that the initial file has plenty of space (minimizing auto-filegrowths during the move.)
• 3 makes sure that the additional files can’t grow to get extra space. That way as much data as possible (see step 5) gets moved into file 1 the first time it gets moved (you don’t want to clear file 5 by moving data into files 1-4, then 4 by moving data into 1-3 etc if you can help it.)
• 2 duh
• 1 collect information.

Demo

-- Set up the demo
-- Don't forget to change the database name and file path to the correct locations for your system
USE [master]
GO
ALTER DATABASE [MultiFileGroups] ADD FILEGROUP [ExtraFG];
GO
ALTER DATABASE [MultiFileGroups] ADD FILE ( NAME = N'MultiFileGroups_ExtraFG_1', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\MultiFileGroups_ExtraFG_1.ndf' , 
SIZE = 5120KB , FILEGROWTH = 10240KB ) TO FILEGROUP [ExtraFG];
ALTER DATABASE [MultiFileGroups] ADD FILE ( NAME = N'MultiFileGroups_ExtraFG_2', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\MultiFileGroups_ExtraFG_2.ndf' , 
SIZE = 5120KB , FILEGROWTH = 10240KB ) TO FILEGROUP [ExtraFG];
ALTER DATABASE [MultiFileGroups] ADD FILE ( NAME = N'MultiFileGroups_ExtraFG_3', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\MultiFileGroups_ExtraFG_3.ndf' , 
SIZE = 5120KB , FILEGROWTH = 10240KB ) TO FILEGROUP [ExtraFG];
ALTER DATABASE [MultiFileGroups] ADD FILE ( NAME = N'MultiFileGroups_ExtraFG_4', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\MultiFileGroups_ExtraFG_4.ndf' , 
SIZE = 5120KB , FILEGROWTH = 10240KB ) TO FILEGROUP [ExtraFG];
ALTER DATABASE [MultiFileGroups] ADD FILE ( NAME = N'MultiFileGroups_ExtraFG_5', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\MultiFileGroups_ExtraFG_5.ndf' , 
SIZE = 5120KB , FILEGROWTH = 10240KB ) TO FILEGROUP [ExtraFG];
GO

-- Add a table and some data
CREATE TABLE ["Which"] (Col1 char(4000)) ON [ExtraFG];
GO
INSERT INTO ["Which"] VALUES (replicate('a',4000));
GO 3000

Step 1: Calculate the space used by each file.

Step2: Some form of backup.

BACKUP DATABASE [MultiFileGroups] 
TO DISK = 'C:\temp\MultiFileGroups.bak'

Step3: Disable auto-growth on all files but number 1.

ALTER DATABASE [MultiFileGroups] MODIFY FILE 
( NAME = N'MultiFileGroups_ExtraFG_2', FILEGROWTH = 0);
ALTER DATABASE [MultiFileGroups] MODIFY FILE 
( NAME = N'MultiFileGroups_ExtraFG_3', FILEGROWTH = 0);
ALTER DATABASE [MultiFileGroups] MODIFY FILE 
( NAME = N'MultiFileGroups_ExtraFG_4', FILEGROWTH = 0);
ALTER DATABASE [MultiFileGroups] MODIFY FILE 
( NAME = N'MultiFileGroups_ExtraFG_5', FILEGROWTH = 0);

Step4: Grow file number 1 if needed based on the information from the first step.

Based on step 1 each of the files has ~2.5MB for a total of ~12.5mb. We want to leave a bit of space extra so let’s grow file 1 to 15mb. If the filegroup held table(s) that grew quickly I might leave extra free space. I also might consider increasing the auto file growth.

ALTER DATABASE [MultiFileGroups] MODIFY FILE 
( NAME = N'MultiFileGroups_ExtraFG_1', SIZE = 15MB )  

Step5: Use DBCC SHRINKFILE TRUNCATE to remove any easy excess space for all files we will remove.

DBCC SHRINKFILE (N'MultiFileGroups_ExtraFG_2' , 0, TRUNCATEONLY)
DBCC SHRINKFILE (N'MultiFileGroups_ExtraFG_3' , 0, TRUNCATEONLY)
DBCC SHRINKFILE (N'MultiFileGroups_ExtraFG_4' , 0, TRUNCATEONLY)
DBCC SHRINKFILE (N'MultiFileGroups_ExtraFG_5' , 0, TRUNCATEONLY)

Step6: Empty and remove the excess files.

Important note here. DBCC SHRINKFILE with EMPTYFILE is fully logged. (Special thanks to Randolph West (b/t) for researching that for me.) If these files are of any reasonable size you should be checking your log space using DBCC SQLPERF(logspace) each time you run these. Take log backups as needed.

-- Run for each file to be removed.  
-- I like to start with the last file and work 
--       backward to keep things neat.
USE [MultiFileGroups]
GO
DBCC SHRINKFILE (N'MultiFileGroups_ExtraFG_5' , EMPTYFILE)
GO
ALTER DATABASE [MultiFileGroups] 
REMOVE FILE [MultiFileGroups_ExtraFG_5]
GO

Possible errors

I make no promises that these are the only errors you will see. They are just the only errors I’ve seen.

DBCC SHRINKFILE: Not all ghost records on the large object page 196:17927 could be removed. If there are active queries on readable secondary replicas check the current ghost cleanup boundary.

Msg 2555, Level 16, State 1, Line 1

Cannot move all contents of file “FileName” to other places to complete the emptyfile operation.

This is an error from DBCC SHRINKFILE. You’ve got ghost records that need to be cleaned up before the shrink can finish. Try again in a few minutes.

Msg 3023, Level 16, State 3, Line 1

Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

This is an error from the ALTER DATABASE command. Every time I’ve run across this one it’s because I hit a scheduled backup. You might find that you need to turn off your scheduled backups if they are frequent enough. Or just time the alter statement really really well. It’s also possible someone else is also trying to run an alter database at the same time you are. But if so then your team needs better coordination, or you neat to remove permissions from your devs.

Final comments

I am not taking into account mirroring or AGs. I honestly am not sure how that would affect the process.

Like any time you run DBCC SHRINKFILE this is going to shred your indexes. Take that into account and re-index as needed.

Filed under: Index, Microsoft SQL Server, SQLServerPedia Syndication Tagged: database files, filegroups, index, microsoft sql server