Remove Defunct Filegroups (SQL Server)
This topic describes how to remove defunct filegroups in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.
In This Topic
Before you begin:
Limitations and Restrictions
Recommendations
Security
To remove defunct filegroups, using:
SQL Server Management Studio
Transact-SQL
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.
Recommendations
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).
Security
Permissions
Requires ALTER permission on the database.
.gif "Arrow icon used with Back to Top link")
[Top]
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.
[Top]
Using Transact-SQL
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 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 AdventureWorks2012
REMOVE FILE test1dat3 ;
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat4 ;
GO
USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILEGROUP Test1FG1 ;
GO
[Top]
See Also
Reference
ALTER DATABASE File and Filegroup Options (Transact-SQL)
Concepts
Deferred Transactions (SQL Server)
File Restores (Full Recovery Model)