Repair Database Strategy for SQL Server 2008 R2 Standard

corruptionsql-server-2008-r2

(one guy pushed green button instead of blue, three racks left as was his original target, probably green is nicer then blue, on this SAN are saved database files)

  • after this accident I'm run consistency check against databases

  • but one of databases returns consistency error

Failed:(-1073548784) Executing the query "DBCC CHECKDB(N'XxxXxxx') WITH NO_INFOMSGS
" failed with the following error: "Extent (1:511776) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Extent (1:511784) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

.
.
.

Extent (1:994048) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:3318703) in object ID 0,
index ID -1, partition ID 0, alloc unit ID 72057594374717440 (type Unknown), but it was not detected in the scan.
CHECKDB found 32446 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 32446 allocation errors and 0 consistency errors in database 'XxxXxx'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XxxXxx).".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.

  • UAT looks like as with success, no data missing

  • exception is (reason why I asked this question) from allocation for a new space on HDD


my question is

  1. may I run standard repair databases (will be my 1st. attempt)

  2. search for details, and run this repair against concrete table(s)

  3. built database from sets of backups and then to import a new data (is possible, in this database there are stored data snapshots)


EDIT

for future readers, there were some issues in my case, (DBCC CHECKTABLE passed with success without any exceptions)

  • SQL Server (both 2008_R2/2012_R2) repair only part of errors by using DBCC CHECKDB(DbName, REPAIR_ALLOW_DATA_LOSS) ….

  • I have to runs this process four times (number of allocated GAMs decreased), last run fixed this problem,

  • interesting is fact that both SQL Servers 2008_R2 and 2012_R2 had the same processes with the same exceptions, number of exceptions, numbers of repairs required, very similair performances (one enviroment with two diff instances)

Best Answer

Seems like you have a serious database corruption as CHECKDB points it out

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB

The only feasible options are :

  1. Restore from backup or
  2. Repair with data loss => Might leave the database in a transactionally inconsistent state.

In both cases, you will have data loss (provided you have good full and T-log backups before that guy pushed green button instead of blue).

I would go for option 1 => Restore from a know latest good backup !

Refer to IAM pages, IAM chains, and allocation units and IAM page corruption examples

UAT looks like as with success, no data missing

If you regularly have UAT in sync with PROD, best is to use that dataset to restore PROD - unless you are masking PROD data in UAT.