2

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!

1

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.

  • thank you! I ran your T-SQL to check for the empty filegroup and got: type_desc = DROPPED; total_pages = 1026; used_pages = 0; data_pages = 0; so it seems empty except for indexes. 1) I would like to try those repair options, although CHECKDB claims REPAIR_ALLOW_DATA_LOSS is the minimum level. I am thinking that would be OK in our situation, and we have good backups. 2) I tried REMOVE FILE, but I get: Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: torn page... – Tony Green Oct 15 '13 at 17:54
1

We were able to fix the corruption using CHECKDB REPAIR_ALLOW_DATA_LOSS.

The first pass repaired 254 allocation errors (8905). Then CHECKDB got stuck on a page (the torn page that started the corruption). It said it fixed the allocation error, but each time I ran it, the error remained. I opened the file in a hex editor and edited the page to make it match a backup we had. Only 16 bytes were different between the corrupt and good pages. That allowed DBCC PAGE to read the torn page and CHECKDB was able to repair an additional 128 allocation errors. After all that, we were able to delete the filegroup.

We had the luxury of being able to take the database offline over the weekend as well as having corruption confined to an empty filegroup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.