database corruption

Question

The database is corrupted and there is no recent backup,

Error from DBCC CHECKDB

Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:72792) contains an incorrect page ID in its page header. The PageId in the page header = (0:0). Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:72792) to (1:80879). See other errors for cause.

1. Any recommendation ?

2. How can i tell which object is corrupted ?

Errors from DBCC CHECKDB (XXX) with NO_INFOMSGS, all_errormsgs

Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:72792) contains an incorrect page ID in its page header. The PageId in the page header = (0:0). Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:72792) to (1:80879). See other errors for cause. CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object. CHECKDB found 2 allocation errors and 0 consistency errors in database 'NSR_M'.

Answer 1

First things first, I hope you have a backup, this is a serious error and you should do a restore, even if you lose some data as that way you will end up with a consistent database but the second best option would be this.

You can peek into the data pages to see what is stored there and maybe, just maybe you can get most of the data from the non damaged tables. Now before we start you should at least read Paul Randal's Inside the Storage Engine: Anatomy of a page and How to use DBCC PAGE. and you should really watch his video on Advanced Data Recovery Techniques

First to make sure what is on the damaged page.

dbcc traceon (3604); 
GO
dbcc page (5,1,73703,0);

This will dump the page header which you can use to decipher what is on the page. From the error message posted there seems to be errors in the GAM/SGAM/PFS for pages 72792-80879 so you can look at which object is stored there by dumping the headers and check the object_id. The syntax for DBCC PAGE is dbcc page (database_id,File_id,PAGE_ID,0); The zero is for dumping the page header but you can dump the whole page by changing that last flag

dbcc page (5,1,72792,0);
GO
dbcc page (5,1,72793,0); 
...

and for each page find to which object it belongs

When you have that information you can hopefully copy the non damaged data from the database into another.

Answer 2

The PageId in the page header = (0:0). Msg 8998, Level 16, State 2, Line 1 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (1:72792) to (1:80879).

Based on the error message, your best bet would be to try doing a select * into new_table from old_table_with_Corruption or bcp out as much data as possible and accept the loss for the data that you cannot bcp out. If you are lucky, you will get some data back, else you have to restore your database to a good backup that you have and accept the data loss.

You might argue that data loss is not allowed, but if data loss is not acceptable then you should have a good backup and tested recovery strategy.

This incident is your time to revisit your backup strategy and implement a routine of your backups to get restored and tested. Also, you can put alerts in place so that SQL Server can alert you if there are CRITICAL Errors.

Answer 3

Basically you are screwed. Your options are:

1. Restore the database from the backup that you have. Hope that it doesn't have corrupt data in it.

2. Export the data from the tables, then restore from backup. Import what you exported and look for the rows which are in the export that aren't in the restored database.

That's pretty much it. You are going to loose data here as the pages which are reporting corruption are the system pages which control what pages are allocated and which ones aren't.