Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

(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)

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
+1 thanks, I have full_backup from yesterday and one todays t-log, my decision is to restore on backup enviroment, looks into extended events for details, then to decide, or to play with both (comprimed backup is cca 12Gb), –  mKorbel Dec 9 '13 at 20:57
    
UAT was internal user acceptance test after failture for all standard procedures, moreover I run SELECT * ... for all tables, but no excpetions, only proccesors & I/O waits are increased :-) –  mKorbel Dec 9 '13 at 21:02
    
@mKorbel proccesors & I/O waits are increased does not even imply corruption. So if UAT is running fine after the disaster, PROD should be in good shape. –  Kin Dec 9 '13 at 21:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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