CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object

Question

Msg 8906, Level 16, State 1, Line 1 Page (1:99122) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:97056), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

Msg 8906, Level 16, State 1, Line 1 Page (1:291360) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:291168), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.

I ran a CHECKDB and received the above error. I have very little experience with SQL errors and our Database team is not an option. I have read that I will have to perform a repair and will incur data loss. Is there anyway to predict what sort of data loss and how much by the above error? Thanks in advance.

• Shanky: At last part of the message did checkdb suggested any repair option ? How big is database ?
• jbartley: CHECKDB found 2 allocation errors and 0 consistency errors in database 'iXData'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (iXData). DBCC execution completed. If DBCC printed error messages, contact your system administrator. The size of the database is 6928.88 MB.
• swasheck: at best it looks like you'll lose two pages of data (if those are the only two entries in the error) but it could be worse. you could attempt to see what's on the page(s) (though if it's not misreferenced, as it appears to be you won't get much) with dbcc traceon(3604);dbcc page('dbname',1,99122,3) where dbname is the name of the database with id=9. maybe you could get some decent info from that. if not too sensitive, you can post this output to the question if you'd like.

Answer 1

My first question would be do you have latest valid backup or not. Since database size is just 6G and also because minimum repair suggested by checkdb is repair_allow_data_loss , which can cause data loss and could also remove business constraints from database, it would be good to restore database from valid backup.

Before restoring the backup you need to check validity of the backup. You can do it using below

restore verifyonly from disk='Backup location'

If it comes out clean you can restore the backup to get back the database. The data loss also depends upon the recovery model of database if it is simple you can only restore full and differential backup if it is bulk logged and Full you can restore full ,differential and log backups to get minimum data loss.

Are you able to take backup of current database with continue after error. Please see if below command works, I guess it would

backup database db_name to disk='Location' with continue_after_error

This would generate corrupt backup of database now restore this backup file ON DIFFERENT SERVER using continue after clause normal restore wont be possible see TSQL Restore for restore command.

After above is done run checkdb within transaction like below on above restored database.

Begin transaction
dbcc checkdb('db_name', repair_allow_data_loss)
--commit

After this you can have look into database how much data checkdb has deleted to recover database. Please note checkdb also removes constraints so I would not ask you to run it. Please read Books Online Document before proceeding

At last please see SQL Server errorlog and event viewer to see why database got corrupted

• jbartley: When checking the backups for this database it appears that the last backup was done on 10/10/2014. I don't think that backing up to that date is going to be a possibility. We would lose several months of data.
• Aaron Bertrand: @jbartley No offense, but this is precisely why we take backups (and test that they can be restored!) if we care about the data at all. You may get away with a repair, if not you may be able to move the data to another database before any more data falls victim, but in either case I hope this serves as an important lesson.
• Shanky: Yes in that case there would be data loss, why you did not took backup for past couple of month ?. I edited my answer to add some more options about restoring corrupt backup on different server and running checkdb in a transaction
• Shanky: But right now responsibility is with you. So could you try option I have mentioned. I agree its case of accidental DBA