Thursday, June 12, 2014

Defunct File Cause SQL Server Upgrade Failure

In the previous post, we discussed how a data file or its filegroup becomes defunct. As making data file defunct may be desirable in certain situation to resolve immediate issue and allow the database continue to be operational, you may encounter some surprise problem when you are ready to move up to the next version during the SQL Server upgrade.

As shown in the previous post that in a situation where a data file is missing or could not be brought online, there is no backup available and has been determined that the data resides in the data file is no longer needed and does not need to be restored, one way to resolve this is making the file/filegroup defunct allows certain database operations like backup to be completed successfully.

Warning. The only way to bring back offline or defunct file online is restore from a backup that was taken prior to making the data file offline/defunct.

As making a data file/filegroup defunct resolve immediate issue, however it may generate problem during SQL Server upgrade. Let's see some example. First we create a database with primary and secondary filegroup on a SQL Server 2008 instance,

Note: If you have performed the steps as in the previous blog post, you may need to delete the example database (testdb), its data files and backups for the steps below.

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' )
LOG ON 
( NAME = testdb_log, 
 FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_log.ldf' );
GO

Creates a table on Primary filegroup and index on the secondary filegroup

USE testdb;
GO

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

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

--Insert data into table and index
INSERT INTO tbl1
VALUES ('ABC');
GO

At the moment, the database has all the data file online. Let's backup the database for later use.

--Backup for later use
BACKUP DATABASE testdb
TO DISK = 'testdb_good_backup.bak';

Make the secondary data file defunct.

--Make the data file defunct
ALTER DATABASE testdb
MODIFY FILE 
( NAME = testdb_file1,
 OFFLINE );
GO

ALTER DATABASE testdb
REMOVE FILEGROUP FG1;
GO

This query below shows that the data file is now in defunct state

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


Take a backup for the database with the defunct file

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

In the newer version of SQL Server instance, in this case SQL Server 2014, restore the database from the backup with the defunct file.

USE master; 
GO

RESTORE DATABASE testdb
FROM DISK = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\testdb_defunct_backup.bak'
WITH 
 MOVE 'testdb_data' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb.mdf',
 MOVE 'testdb_file1' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb_file1.ndf',
 MOVE 'testdb_log' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb_log.ldf';
GO 


The metadata of the index in defunct file doesn't affect the restore process.The database restored and upgraded successfully. Of course it will return error if the index is queried on the new version instance, just like in the original SQL Server instance.

Let's take another example. This time with the full text index. First, restore the database back to the status before the secondary data file becomes defunct. We will restore from the good backup file.

USE master;
GO

ALTER DATABASE testdb
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

--Restore the database back to prior defunct
RESTORE DATABASE testdb
FROM DISK = 'testdb_good_backup.bak'
WITH REPLACE;
GO

Create fulltext catalog and fulltext index on filegroup FG1

USE testdb;
GO

--Create fulltext catalog
CREATE FULLTEXT CATALOG catalog1 AS DEFAULT;
GO

--Create fulltext index on filegroup FG1
CREATE FULLTEXT INDEX ON tbl1 (col2)
KEY INDEX PK_tbl1_col1
ON (FILEGROUP FG1);
GO

Now make the data file defunct like how it was done previously.

-Make the data file defunct
ALTER DATABASE testdb
MODIFY FILE 
( NAME = testdb_file1,
 OFFLINE );
GO

ALTER DATABASE testdb
REMOVE FILEGROUP FG1;
GO

Take another backup. The backup consist of defunct data file with fulltext index.

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

In the new version of SQL Server instance, drop the testdb database and restore from the backup just taken.

USE master; 
GO

ALTER DATABASE testdb
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

DROP DATABASE testdb;
GO

RESTORE DATABASE testdb
FROM DISK = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\testdb_defunct_fulltext_backup.bak'
WITH 
 MOVE 'testdb_data' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb.mdf',
 MOVE 'testdb_file1' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb_file1.ndf',
 MOVE 'testdb_log' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb_log.ldf';
GO 

The restore operation failed with error.

Processed 184 pages for database 'testdb', file 'testdb_data' on file 1. 
Processed 0 pages for database 'testdb', file 'testdb_file1' on file 1. 
Processed 7 pages for database 'testdb', file 'testdb_log' on file 1. 
Converting database 'testdb' from version 661 to the current version 782. 
Database 'testdb' running the upgrade step from version 661 to version 668. 
Database 'testdb' running the upgrade step from version 668 to version 669. 
Database 'testdb' running the upgrade step from version 669 to version 670. 
Database 'testdb' running the upgrade step from version 670 to version 671. 
Database 'testdb' running the upgrade step from version 671 to version 672. 
Msg 667, Level 16, State 1, Line 24 The index "" for table "" (RowsetId 72057594039369728) resides on a filegroup ("FG1") that cannot be accessed because it is offline, is being restored, or is defunct. 
Msg 3013, Level 16, State 1, Line 24 
RESTORE DATABASE is terminating abnormally.

and also this error in Windows application log and SQL Server log,

During upgrade, database raised exception 3602, severity 25, state 53, address 000007FED2F36365. Use the exception number to determine the cause.

If we pay closer attention, we can see the the restore step completed successfully. The failure is during the conversion (upgrade) step.

With the query below on the original SQL Server instance, it shows that the object is one of the ifts (integrated full text search) internal table.

SELECT 
 p.partition_id,
 o.name [object_name], 
 o.type_desc,
 f.name [filegroup]
FROM sys.allocation_units a
JOIN sys.partitions p
 ON a.container_id = p.hobt_id
JOIN sys.filegroups f
 ON a.data_space_id = f.data_space_id
JOIN sys.objects o
 ON o.object_id = p.object_id
WHERE p.partition_id = '72057594039369728'


Observation
As the data file becomes defunct, metadata of the objects in the defunct data file retained. SQL Server upgrade process attempts to upgrade objects (eg. fulltext index) to newer version including the ones in the defunct data file (as the metadata is retained), it leads to the upgrade failure. However, even the fulltext index was determined as no longer needed, it couldn't be removed as the data file is in defunct state and on the other hand it causes error during the upgrade. In this predicament, the only solution appears to be extract everything (minus the ones in defunct data file) from the existing database to a new database.

This issue is observed on upgrade from SQL Server 2008 to SQL Server 2012 and SQL Server 2014. Restore from SQL Server 2008 to SQL Server 2008 R2 doesn't have any problem as there is no upgrade step involved during the restore operation between these two versions.

Google+