Removing offline/defunct files in SQL server 2008

How to remove traces of files marked as OFFLINE or DEFUNCT in Microsoft SQL server 2008?

I have been playing around with a setup where I create a database with 3 file-groups which are: Primary, FileGroupData and FileGroupIndex. The clustered index is using FileGroupData and a non-clustered index is set to use FileGroupIndex. To simulate a disk failure I've shut down SQL server and manually deleted the files in index file-group. To start the database I'll mark the files 'OFFLINE', but after that I can't delete the index files, which are now offline.

I don't have backup of the files as they are merely indices, but that has the implication that I can't restore the files and have their status as "ONLINE".

How would you recommend removing the files and the file-group as they still show up in management studio under files/file-groups. Management studio is not able to delete them.

As far as I can tell this is different from the question posted in : http://stackoverflow.com/questions/462637/how-do-i-remove-offline-files-from-a-sql-server-2005-database

/Philip

Thanks for the input.I was hoping to avoid dropping the database. Only files used by a non-clustered index are offline and that index can easily be rebuilt./Philip

2010-04-13 08:35:22

@philox: no, it is corrupted. You may know what is wrong, but SQL Server does not distinguish such subtleties when a whole file is missing.

gbn 2010-04-14 05:35:28

@gbn: You could be right. It most be somewhat aware though, since it can be partially available and disallow queries that would use an index that resides on an OFFLINE/DEFUNCT filegroup. If the filegroup was online management studio would be able to delete it right?

2010-04-14 07:08:34

From the following I guess it boils down if its possible to remove the metadata?http://msdn.microsoft.com/en-us/library/ms175122.aspx "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."

2010-04-14 07:22:11

Tibor Karaszi has posted in the following thread, that he doesn't think it's possible to remove (without recreating the scheme and moving data). http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/e11a8401-5058-45e2-8f06-4db45cab3eab

2010-04-14 07:29:36

@philox: I would probably restore rather than salvage, depending on the situation. Then fix the disk issue. A missing file on disk = bollixed to me

gbn 2010-04-14 07:52:06

@gbn: I think I have to agree. I got "blinded" by the prospect of salvaging the database, which is huge, since restoring it would require actual backups of the indices as well. Thanks for your feedback.

2010-04-15 12:00:39

ansaurus

tags:

views:

answers:

Removing offline/defunct files in SQL server 2008

related questions