Sql-server – Delete corrupt empty filegroup

sql-server-2005

We have a large database (1.8TB) with eight filegroups created on SQL 2000 and now running on SQL Server 2005 9.0.5057 since early 2012. Simple recovery.

On September 27, we got a torn page error in one of the filegroups and error 824. The vendor of the third-party app that uses this database asked us to restore that filegroup to a new database, which I did over that weekend. The vendor created a new filegroup and copied everything from the restored filegroup to repair the production database. Everything seems fine operationally, but we still have the corruption in the old empty filegroup.

CHECKDB found 254 allocation errors and 0 consistency errors.

There are also 9 page errors like this:
Msg 8906, Level 16, State 1, Server ANNIE, Line 1
Page (5:4114) in database ID 5 is allocated in the SGAM (5:3) and PFS
(5:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT
ALLOCATED 0_PCT_FULL'.

Only one of those 9 pages is listed in suspect_pages.

I ran DBCC PAGE on the 9 pages in the CHECKDB output. 8 pages are type 2 and 1 is type 10. All list object ID as m_objId (AllocUnitId.idObj) = 41899 and OBJECT_NAME(41899) returns null.

Also all the errors are on file_id 5, which is the filegroup that is now empty.

It seems like if we could delete the corrupt filegroup, we would be OK, but SHRINKFILE and REMOVE FILE fail because of the torn page error.

Is there any other way to delete the empty filegroup?

Thanks for any advice!

Best Answer

Make sure the file group is actually empty by looking at 

SELECT * 
FROM sys.allocation_units AS AU
JOIN sys.filegroups AS F 
ON AU.data_space_id = F.data_space_id
ORDER BY F.name;

If it is empty:

1) Try one of the DBCC CHECKDB repair options (REPAIR_FAST or REPAIR_REBUILD). If you are sure that there is no corruption of any type in any other file it should even be safe to do a "REPAIR_ALLOW_DATA_LOSS" but make sure you have a good backup when using that last one. (It is a very good idea to have a backup anyway). Save all the output you get for later analysis. If any repair was successful empty the files as before.

2) If that did not help you should be able to just drop the files: http://technet.microsoft.com/en-us/library/ms175122.aspx However, that might leave the database in some kind of "funky" state (see above link), so I would try 1) first.

Related Question