Remove Defunct Filegroups (SQL Server)

Applies to: SQL Server

This topic describes how to remove defunct filegroups in SQL Server by using SQL Server Management Studio or Transact-SQL.

In This Topic

Before You Begin

This topic is relevant for SQL Server databases that contain multiple files or filegroups; and, under the simple model, only for read-only filegroups.
All files in a filegroup become defunct when an offline filegroup is removed.

If an unrestored filegroup will never have to be restored, you can make the filegroup defunct by removing it from the database. The defunct filegroup can never be restored to this database, but its metadata remains. After the filegroup is defunct, the database can be restarted, and recovery will make the database consistent across the restored filegroups.

For example, making a filegroup defunct is an option for resolving deferred transactions that were caused by an offline filegroup that you no longer want in the database. Transactions that were deferred because the filegroup was offline are moved out of the deferred state after the filegroup becomes defunct. For more information, see Deferred Transactions (SQL Server).

Requires ALTER permission on the database.

In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
Expand Databases, right-click the database from which to delete the file, and then click Properties.
Select the Files page.
In the Database files grid, select the files to delete, click Remove, and then click OK.
Select the Filegroups page.
In the Rows grid, select the filegroup to delete, click Remove, and then click OK.

Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. (Note: This example assumes that the files and filegroup already exist. To create these objects, see example B in the ALTER DATABASE File and Filegroup Options topic.) The first example removes the test1dat3 and test1dat4 files from the defunct filegroup by using the ALTER DATABASE statement with the REMOVE FILE clause. The second example removes the defunct filegroup Test1FG1by using the REMOVE FILEGROUP clause.

USE master;  
GO  
ALTER DATABASE AdventureWorks2022  
REMOVE FILE test1dat3 ;  
ALTER DATABASE AdventureWorks2022  
REMOVE FILE test1dat4 ;  
GO

USE master;  
GO  
ALTER DATABASE AdventureWorks2022  
REMOVE FILEGROUP Test1FG1 ;  
GO