Sql-server – CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object

corruptiondbcc-checkdbsql server

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.

Best Answer

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

Related Question