SQL Server - Defunct Filegroup and Defunct Data File

A data file becomes DEFUNCT when its respective filegroup is removed or when the data file or its filegroup is not included during the piecemeal restore in simple recovery model. Usually a filegroup can not be removed if the data file is not empty. However, a filegroup could be removed if one of its data file is not online. This post illustrates how a data file becomes DEFUNCT.

Let's create a database and show how a data file becomes DEFUNCT.

USE master;
GO

--Create database
CREATE DATABASE testdb
ON PRIMARY
( NAME = testdb_data, 
 FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb.mdf' ),
FILEGROUP FG1
( NAME = testdb_file1, 
 FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_file1.ndf' ),
( NAME = testdb_file2, 
 FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_file2.ndf' )
LOG ON 
( NAME = testdb_log, 
 FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_log.ldf' );
GO

--Set database to simple recovery model
ALTER DATABASE testdb
SET RECOVERY SIMPLE;
GO

The query above creates a database with the PRIMARY filegroup and FG1 secondary filegroup. The PRIMARY filegroup has one data file (mdf) and FG1 filegroup has two data files (ndf). The database is also set to simple recovery model.

Next, we creates some data.

USE testdb;
GO

--Create table on primary filegroup
CREATE TABLE tbl1
(
 col1 int IDENTITY (1,1) CONSTRAINT PK_tbl1_col1 PRIMARY KEY,
 col2 char(800)
);
GO

--Create index on FG1 filegroup
CREATE INDEX IX_tbl1_col2
ON tbl1
(
 col2
) ON FG1;
GO

--Insert enough data so both secondary files in FG1 are filled with some data
INSERT INTO tbl1
VALUES ('A');
GO 120

The query above created the table is created on PRIMARY filegroup, while its index is created on FG1 filegroup.

SELECT 
 o.name tbl_name,
 i.name index_name,
 i.type_desc,
 f.name fg_name
FROM sys.objects o
JOIN sys.indexes i
 ON o.object_id = i.object_id
JOIN sys.filegroups f
 ON i.data_space_id = f.data_space_id
WHERE o.is_ms_shipped = 0;
GO

The query above verified which filegroup the data is stored at. Now we try to make the data file in FG1 defunct. Let's first take a good backup for later use.

BACKUP DATABASE testdb
TO DISK = 'testdb_good_backup.bak';
GO

Now, takes the first secondary file (testdb_file1) offline.

ALTER DATABASE testdb
MODIFY FILE 
( NAME = testdb_file1,
 OFFLINE );
GO

Warning. Please beware that the only way to bring the data file back online is restore from a backup.

SELECT 
 f.name file_group, 
 d.name file_name, 
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO

The query above shows that the testdb_file1 data file is now offline. That also makes the FG1 filegroup offline as well. Any attempt to query data located on the offline filegroup returns error. The query below force the query to use the index located in the offline filegroup.

SELECT col2
FROM dbo.tbl1 
WITH (INDEX(IX_tbl1_col2));
GO

Msg 315, Level 16, State 1, Line 56 Index "IX_tbl1_col2" on table "dbo.tbl1" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

What about backing up the database at this moment?

BACKUP DATABASE testdb
TO DISK = 'testdb_backup.bak';
GO

Msg 3007, Level 16, State 1, Line 115
The backup of the file or filegroup "testdb_file1" is not permitted because it is not online. Container state: "Offline" (7). Restore status: 0. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
Msg 3013, Level 16, State 1, Line 115 
BACKUP DATABASE is terminating abnormally.

The only way to bring the data file back online is restore the database from the backup. You should always do that. However, if there is no backup and it has been determined that data in the offline filegroup is not needed, one way to allow backup is to make the filegroup defunct. That will remove the filegroup from the database but retain the metadata.

ALTER DATABASE testdb
REMOVE FILEGROUP FG1;
GO

Now if we check the status of the data files.

SELECT 
 f.name file_group, 
 d.name file_name, 
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO

Even the testdb_file2 secondary file is online and is not empty, the FG1 filegroup removal completed successfully. Notice that both secondary files in FG1 filegroup are now in DEFUNCT state. Query against data located in this filegroup returns same error.

BACKUP DATABASE testdb
TO DISK = 'testdb_defunct_backup.bak';
GO

Backup could be performed successful now with the understanding the data in the FG1 filegroup is no longer available.

As mentioned earlier, another way data files become DEFUNCT is during the piecemeal restore for database in simple recovery model. In this case, we restore the good testdb backup (testdb_good_backup.bak) taken earlier and restore only the primary filegroup to a new database (testdb_copy).

RESTORE DATABASE testdb_copy
FILEGROUP = 'PRIMARY'
FROM DISK = 'testdb_good_backup.bak'
WITH 
 MOVE 'testdb_data' TO 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_copy.mdf',
 MOVE 'testdb_log' TO 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_copy_log.ldf',
 PARTIAL;
GO

Processed 184 pages for database 'testdb_copy', file 'testdb_data' on file 1. Processed 2 pages for database 'testdb_copy', file 'testdb_log' on file 1. 
Msg 3127, Level 16, State 1, Line 75 
The file 'testdb_file1' of restored database 'testdb_copy' is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore. 
Msg 3127, Level 16, State 1, Line 75 
The file 'testdb_file2' of restored database 'testdb_copy' is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore. 
RESTORE DATABASE ... FILE= successfully processed 186 pages in 0.049 seconds (29.595 MB/sec).

Running the below script shows the two secondary files in FG1 filegroup in DEFUNCT state.

USE testdb_copy;
GO

SELECT 
 f.name file_group, 
 d.name file_name, 
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO

Note that the new database is piecemeal restore from a good complete backup. Full restore from the backup (testdb_defunct_backup.bak) taken after the defunct files creates database with the secondary files in DEFUNCT state, just like how it was.

Summary, data file becomes DEFUNCT when its filegroup is removed when one of its data file is not online. This action may be intentionally taken to allow tasks like backup to perform successfully when the offline file could not be found and there is no backup and more importantly, the data in the filegroup has been determined not needed or restored. Another situation is during piecemeal restore for database in simple recovery model that left the non-recovered file or filegroup in DEFUNCT state.

DEFUNCT data files affects SQL Server upgrade in some cases. More detail in next post.