Defunct Filegroups

 This topic is relevant for SQL Server databases that contain multiple files or filegroups; and, under the simple model, only for read-only filegroups.

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.

To make a filegroup defunct

1. Remove all of the files from the filegroup by using the following ALTER DATABASE statement:

   ALTER DATABASE database_name REMOVE FILE file_name

2. Remove the filegroup by using the following ALTER DATABASE statement:

   ALTER DATABASE database_name REMOVE FILEGROUP filegroup_name

See Also

Concepts

Factors That Can Delay Log Truncation

Deferred Transactions

Performing File Restores (Full Recovery Model)

Performing File Restores (Simple Recovery Model)

Performing Online Restores

Performing Page Restores

Performing Piecemeal Restores