none
Indici DB corrotti RRS feed

  • Domanda

  • Salve, ultimamente ho notato strani errori su select e rebuild degli indici in un database. Durante alcune select, la connessione cade dando l’errore:

    “A transport-level error has occurred when receiving results from the server.”

    Ho eseguito il checkdb sul database, il quale riporta errori sui dati (qui sotto ho riportato alcuni messaggi di errore).

    Anche altri database sullo stesso server riportano errori di consistenza sugli indici.

    Come potrei procedere (andandoci con i piedi di piombo) per ripristinare la situazione alla normalità?

    Executing the query "DBCC CHECKDB(N'DataBaseName') WITH NO_INFOMS..." failed with the following error: "Table error: Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data). The off-row data node at page (1:31821), slot 0, text ID 1369309184 does not match its reference from page (3:722132), slot 0.

    Table error: Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data). The off-row data node at page (1:31821), slot 0, text ID 1369309184 does not match its reference from page (3:722132), slot 0.

    Table error: Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data). The off-row data node at page (1:31821), slot 0, text ID 0 is pointed to by page (3:722132), slot 0 and by page (3:1050697), slot 0.

    Table error: Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data). The off-row data node at page (1:337703), slot 0, text ID 362545152 is referenced by page (3:722132), slot 0, but was not seen in the scan.

    Table error: Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data). The off-row data node at page (1:768400), slot 0, text ID 273612800 does not match its reference from page (3:722132), slot 0.

    Table error: Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data). The off-row data node at page (1:768400), slot 0, text ID 0 is pointed to by page (3:70789), slot 3 and by page (3:722132), slot 0.

    Table error: Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data). The off-row data node at page (1:768401), slot 0, text ID 487653376 does not match its reference from page (3:722132), slot 0.

    Table error: Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data). The off-row data node at page (1:768401), slot 0, text ID 487653376 does not match its reference from page (3:722132), slot 0.

    Table error: Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data). The off-row data node at page (1:768401), slot 0, text ID 0 is pointed to by page (3:722132), slot 0 and by page (3:71453), slot 1.

    Table error: Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data). The off-row data node at page (1:768402), slot 0, text ID 1618214912 does not match its reference from page (3:722132), slot 0.

    Table error: Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data). The off-row data node at page (1:768402), slot 0, text ID 0 is pointed to by page (3:72363), slot 1 and by page (3:722132), slot 0.

    Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data): Errors found in off-row data with ID 487653376 owned by data record identified by RID = (3:1487:0)

    Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data): Errors found in off-row data with ID 487653376 owned by data record identified by RID = (3:27550:0)

    Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data): Errors found in off-row data with ID 487653376 owned by data record identified by RID = (3:56032:3)

    Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data): Errors found in off-row data with ID 487653376 owned by data record identified by RID = (3:64718:1)

    Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data): Errors found in off-row data with ID 487653376 owned by data record identified by RID = (3:65611:0)

    Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data): Errors found in off-row data with ID 487653376 owned by data record identified by RID = (3:65828:1)

    Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data): Errors found in off-row data with ID 487653376 owned by data record identified by RID = (3:66240:3)

    Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data): Errors found in off-row data with ID 273612800 owned by data record identified by RID = (3:70789:3)

    Object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data): Errors found in off-row data with ID 487653376 owned by data record identified by RID = (3:71453:1)

    giovedì 15 marzo 2012 11:08

Tutte le risposte

  • Ho eseguito il checkdb sul database, il quale riporta errori sui dati (qui sotto ho riportato alcuni messaggi di errore).

    Anche altri database sullo stesso server riportano errori di consistenza sugli indici.

    Come potrei procedere (andandoci con i piedi di piombo) per ripristinare la situazione alla normalità?

    Ciao,

    Se diversi db hanno problemi di consistenza il problema è probabilmente HW a livello di controller o sottositema dischi.

    La prima cosa che ti consiglio di fare è eseguire un full backup di tutto (db utente e di sistema) salvandolo su uno storage esterno (es. una share di rete), seguito da un check approfondito dell'HW con apposite utility. Se riscontri errori sostituisci i pezzi guasti e riesegui i ckeck.

    A questo punto l'unico metodo "con i piedi di piombo" per rispristinare i db è quello di eseguire un restore degli ultimi backup non corrotti, diversamente puoi tentare di "riparare" i db mediante DBCC CHECKDB specificando l'opzione REPAIR_REBUILD e se non risolvessi REPAIR_ALLOW_DATA_LOSS che comporta però la perdita di alcuni dati.

    Ad ogni modo ricorda che senza un backup valido non è detto che tu riesca a risolvere il problema...

    Ciao!


    Lorenzo Benaglia
    Microsoft MVP - SQL Server
    http://blogs.dotnethell.it/lorenzo
    http://social.technet.microsoft.com/Forums/it-IT/sqlserverit

    giovedì 15 marzo 2012 19:24
    Moderatore
  • Ciao,
    grazie per i preziosi consigli, non ho avuto modo di applicarli di persona perchè quelli che comandano avevano già preso accordi 
    per l'intervento di una società esterna, kroll ontrack, ad ogni modo la strada suggerita era quella corretta. L'intervento è
    avvenuto tramite un servizio di remote data recovery (una sorta di remote desktop) e se può essere utile a qualcuno riporto cosè stato fatto.
    E' stata utilizzata l’utility SqlIoSim per verificare eventuali errori di I/O che ha segnalato molti messaggi d’errore : 
    “Buffer validation failed on C:\sqliosim.mdx Page: 59659, offset 0x8”
    “Dump file successfully written: C:\SQLIOSimX64\SqlSimErrorDump00001.txt”
    “Buffer validation failed on C:\sqliosim.mdx Page: 59660, offset 0x8”
    “Dump file successfully written: C:\SQLIOSimX64\SqlSimErrorDump00002.txt”
    etc...


    Da un'ulteriore analisi la causa di questi errori viene individuata nel controller RAID, per un impostazione della cache in lettura: 
    disabilitando la modalità read-ahead, SqlIoSim non da più errori.
    E' stato portato il database in modalità recovery (SINGLE_USER) ed eseguito il repair. 
    Come avevi predetto, il repair comporterà la perdita di alcuni dati (REPAIR_ALLOW_DATA_LOSS).
    Il repair viene eseguito correttamente, ma come previsto, vengono perse 88 righe:


    Repair: The Clustered index successfully rebuilt for the object "dbo.tablename" in database "dbName".
    Repair: Deleted off-row data column with ID 1369309184, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data) on page (1:31821), slot 0.
    Repair: Deleted off-row data column with ID 1369309184, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data) on page (1:31821), slot 0.
    Repair: Deleted off-row data column with ID 273612800, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data) on page (1:768400), slot 0.
    Repair: Deleted off-row data column with ID 487653376, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data) on page (1:768401), slot 0.
    Repair: Deleted off-row data column with ID 1618214912, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057594057457664 (type LOB data) on page (1:768402), slot 0.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:1487),  slot 0. Indexes will be rebuilt.
    Repair: Deleted off-row data column with ID 487653376, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data) on page (3:1487), slot 0.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:27550),  slot 0. Indexes will be rebuilt.
    Repair: Deleted off-row data column with ID 487653376, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data) on page (3:27550), slot 0.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:56032),  slot 3. Indexes will be rebuilt.
    Repair: Deleted off-row data column with ID 487653376, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data) on page (3:56032), slot 3.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:64718),  slot 1. Indexes will be rebuilt.
    Repair: Deleted off-row data column with ID 487653376, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data) on page (3:64718), slot 1.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:65611),  slot 0. Indexes will be rebuilt.
    Repair: Deleted off-row data column with ID 487653376, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data) on page (3:65611), slot 0.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:65828),  slot 1. Indexes will be rebuilt.
    Repair: Deleted off-row data column with ID 487653376, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data) on page (3:65828), slot 1.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:66240),  slot 3. Indexes will be rebuilt.
    Repair: Deleted off-row data column with ID 487653376, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data) on page (3:66240), slot 3.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:70789),  slot 3. Indexes will be rebuilt.
    Repair: Deleted off-row data column with ID 273612800, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data) on page (3:70789), slot 3.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:71453),  slot 1. Indexes will be rebuilt.
    Repair: Deleted off-row data column with ID 487653376, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data) on page (3:71453), slot 1.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:72363),  slot 1. Indexes will be rebuilt.
    Repair: Deleted off-row data column with ID 1618214912, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data) on page (3:72363), slot 1.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:89695),  slot 0. Indexes will be rebuilt.
    Repair: Deleted off-row data column with ID 487653376, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data) on page (3:89695), slot 0.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:102968),  slot 1. Indexes will be rebuilt.
    Repair: Deleted off-row data column with ID 487653376, for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data) on page (3:102968), slot 1.
    Repair: Deleted record for object ID 455672671, index ID 1, partition ID 72057684848410624, alloc unit ID 72057685320138752 (type In-row data), on page (3:135468),  slot 3. Indexes will be rebuilt.
    etc...


    In seguito le righe che erano state perse si è riuscito a recuperarle ripristinando un precedente backup, comunque danneggiato che però è servito lo stesso per i dati che avevamo perso.
    Grazie ancora per il supporto, la prossima volta se dovesse accadere di nuovo spero di poter rimediare in prima persona.

    martedì 20 marzo 2012 17:21