Remove Defunct Filegroups (SQL Server)
This topic describes how to remove defunct filegroups in SQL Server 2017 by using SQL Server Management Studio or Transact-SQL.
In This Topic
Before you begin:
To remove defunct filegroups, using:
Before You Begin
Limitations and Restrictions
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.
Using SQL Server Management Studio
To remove defunct filegroups
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.
We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. You can click the This page button in the Feedback section at the bottom of this page. We read every item of feedback about SQL, typically the next day. Thanks.
To remove defunct filegroups
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
test1dat4files from the defunct filegroup by using the
ALTER DATABASEstatement with the
REMOVE FILEclause. The second example removes the defunct filegroup
Test1FG1by using the
USE master; GO ALTER DATABASE AdventureWorks2012 REMOVE FILE test1dat3 ; ALTER DATABASE AdventureWorks2012 REMOVE FILE test1dat4 ; GO
USE master; GO ALTER DATABASE AdventureWorks2012 REMOVE FILEGROUP Test1FG1 ; GO
ALTER DATABASE File and Filegroup Options (Transact-SQL)
Deferred Transactions (SQL Server)
File Restores (Full Recovery Model)
File Restores (Simple Recovery Model)
Online Restore (SQL Server)
Restore Pages (SQL Server)
Piecemeal Restores (SQL Server)