Remove Defunct Filegroups (SQL Server)
Applies To: SQL Server 2016
This topic describes how to remove defunct filegroups in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL.
In This Topic
Before you begin:
To remove defunct filegroups, using:
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
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
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)