Cet article fait suite à une question postée sur developpez.net la semaine dernière. La personne indique qu’une erreur 823 est remontée sur une base en version SQL Server 2000. On lui demande d’inspecter les logs systèmes à la recherche d’une panne matérielle qui aurait pu être à l’origine du problème, puis de retourner le résultat d’un DBCC CHECKDB sur la base:
Msg*8909, Niveau*16, État*1, Ligne*1
Erreur de TABLE : Objet ID = 0, INDEX ID*=*0, page ID = (1:22988). ID de page de l'en-tête
de page = (0:0).
CHECKDB a trouvé 0 erreurs d'allocation et 1 erreurs de cohérence non associées à un
quelconque objet UNIQUE.
Msg*8928, Niveau*16, État*1, Ligne*1
Objet ID = 574885365, INDEX ID = 0 : La page (1:22988) ne peut pas être traitée. Pour
plus d'informations, consultez les autres erreurs.
Msg*8976, Niveau*16, État*1, Ligne*1
Erreur de table : Objet ID = 574885365, index ID = 1. Page (1:22988) n'a pas été trouvé
dans l'analyse bien que ses parents (1:22930) et (1:22987) précédents y font référence.
Contrôlez toutes les erreurs précédentes.
Msg*8978, Niveau*16, État*1, Ligne*1
Erreur de table : Objet ID = 574885365, index ID = 1. La page (1:22989) n'a pas de référence
dans la page précédente (1:22988). Possibilité d'un problème de liaison de chaîne.
CHECKDB a trouvé 0 erreurs d'allocation et 3 erreurs de cohérence dans la TABLE
'XXXXXXXX' (objet ID = 574885365).
CHECKDB a trouvé 0 erreurs d'allocation et 4 erreurs de cohérence dans la base de
données 'xxxxxxx'.
repair_allow_data_loss est le minimum de niveau de réparation pour les erreurs trouvés
par DBCC CHECKDB (chantier ).Outre le fait qu’un problème matériel empêche tout bonnement la lecture d’une page depuis le disque, le CHECKDB indique deux autres erreurs 8978 et 8976, parlant d’un problème de liaison de page. On rappelle que chaque page de données ou d’index hors pages spéciales (IAM, DBINFO, SGAM, GAM, PFS, ML, DM,…) est liée à la page qui la précède et à la page qui la suit dans le plan d’allocation, mais aussi à une page parente dans un index.
Lorsqu’une page parente référence une page à un niveau inférieur du B-tree et que cette page n’est pas trouvée, alors DBCC CHECKDB renvoie une erreur 8976. Lorsqu’une page référence une page Next et que cette page n’est pas trouvée, alors DBCC CHECKDB renvoie une erreur 8978. Mais ces messages n’indiquent pas s’il ne s’agit que d’une corruption de l’entête de la page, ou aussi des données.
Ce qui veut dire que les données peuvent être bonnes alors que DBCC CHECKDB nous oriente vers un repair_allow_data_loss comme méthode de résolution, avec les conséquences que l’on sait (désallocation de la page qui pose problème et perte des données).
Dans le cas évoqué sur developpez.net, la personne ne dispose pas de backup, la question est de savoir quelles données il risque de perdre, et si les données ne sont pas endommagées, comment peut-il les récupérer.
Repro pour ce problème
Nous allons créer une base linkcorrupt et modifier la valeur du pointeur prevPageID dans une page de niveau feuille d’un index cluster pour forcer des erreurs 8976 et 8978.
create database linkcorrupt GO use linkcorrupt GO create table T1( a numeric identity, b char(4000) default replicate('b',4000), c bigint default round(rand()*100,0)) GO insert into T1 default values GO 1000 create unique clustered index IDX_T1C on T1(a) GO
On va choisir une page du niveau feuille de l’index cluster (le dbcc ind a été purgé de certaines colonnes pour une meilleure lisibilité)
dbcc ind('linkcorrupt','T1',-1) PageFID PagePID PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- -------- ---------- ----------- ----------- ----------- -----------
1 90 10 NULL 0 0 0 0
1 110 2 1 1 115 0 0
1 115 2 1 0 0 1 110
1 121 2 2 0 0 0 0
1 672 1 0 1 673 0 0
1 673 1 0 1 674 1 672
1 674 1 0 1 675 1 673
1 675 1 0 1 676 1 674
1 676 1 0 1 677 1 675
1 677 1 0 1 678 1 676
1 678 1 0 1 679 1 677
1 679 1 0 1 680 1 678
1 680 1 0 1 681 1 679
...
De ce résultat on déduit que:
- La page 1:90 est la page IAM pour T1.
- La page 1:121 est la page racine de l’index cluster
- Les pages 1:110 et 1:115 sont des pages de niveau intermédiaire pour l’index cluster (on aura volontairement casé deux lignes par page pour augmenter le feuillage de l’index).
- Les autres pages sont les pages du niveau feuille de l’index cluster. On choisira de corrompre le pointeur prevPagePID dans la page 1:674. Comme l’index vient juste d’être construit, il est parfaitement clusterisé et les pages avant et après sont respectivement 1:673 et 1:675. On passe la base hors-ligne et on ouvre le fichier MDF dans xvi32.
alter database linkcorrupt set offlineLa page 1:674 se trouve à l’offset 0x544000 dans le fichier mdf (674*8192). Il est alors aisé de repérer les offset où sont stockés les pointeurs prev et next:
Le trait rouge représente l’offset 0 de la page 1:674. Les 96 premiers octets constituent l’entête de la page. x86 étant en little-endian, l’octet de poids faible est en premier donc il faut lire à l’envers. 02A1 représente notre page prev 1:673, 02A3 notre page next 1:675 et 02A2 notre PageID. On décide de zéro-initialiser l’octet de poids faible de 673 :
On repasse alors la base en ligne et on contrôle notre base pour vérifier qu’elle se trouve bien dans l’état attendu:
alter database linkcorrupt set online
GO
dbcc checkdb ('linkcorrupt') with no_infomsgs, all_errormsgs
GO
Msg 8928, Niveau 16, État 1, Ligne 1
ID d'objet 2073058421, ID d'index 1, ID de partition 72057594038386688, ID d'unité d'allocation 72057594043367424 (type In-row data) :
impossible de traiter la page (1:674). Pour plus d'informations, consultez les autres erreurs.
Msg 8939, Niveau 16, État 98, Ligne 1
Erreur de table : ID d'objet 2073058421, ID d'index 1, ID de partition 72057594038386688, ID d'unité d'allocation 72057594043367424
(type In-row data), page (1:674). Échec du test (IS_OFF (BUF_IOERR, pBUF->bstat)). Valeurs 12716041 et -4.
Msg 8976, Niveau 16, État 1, Ligne 1
Erreur de table : ID d'objet 2073058421, ID d'index 1, ID de partition 72057594038386688, ID d'unité d'allocation 72057594043367424
(type In-row data). La page (1:674) n'a pas été détectée lors de l'analyse alors que sa page parent (1:110) et les (1:673) pages précédentes
la référencent. Vérifiez les erreurs précédentes.
Msg 8978, Niveau 16, État 1, Ligne 1
Erreur de table : ID d'objet 2073058421, ID d'index 1, ID de partition 72057594038386688, ID d'unité d'allocation 72057594043367424
(type In-row data). Une référence de la page précédente (1:674) est manquante à la page (1:675). Un problème de liaison de chaîne s'est
peut-être produit.
CHECKDB a trouvé 0 erreurs d'allocation et 4 erreurs de cohérence dans la table 'T1' (ID d'objet 2073058421).
CHECKDB a trouvé 0 erreurs d'allocation et 4 erreurs de cohérence dans la base de données 'linkcorrupt'.
repair_allow_data_loss est le niveau minimum de réparation pour les erreurs trouvées par DBCC CHECKDB (linkcorrupt).Identifier les plages de valeurs concernées par la corruption:
Une des questions qu’on peut se poser, comment savoir ce qu’il y a à récupérer (si les données ne sont pas touchées) ou ce qu’on va potentiellement perdre (si les données sont touchées).
Avant cela, il faut déjà récupérer tout le reste de la table en injectant les données jusqu’à la page 1:673 comprise et à partir de la page 1:675 comprise, puis d’inspecter la page 1:674 pour voir si les données qui y sont contenues sont lisibles. On sait que les pages sont classées physiquement dans l’ordre de notre clé clusterisée, donc il suffit pour exclure l’intervalle compris dans la page 1:674, de connaître la dernière valeur de ‘a’ dans la page 1:673 et la première valeur de ‘a’ dans la page 1:675.
On créé deux tables temporaires pour stocker le résultat des dbcc page sur les deux pages prev et next:
CREATE TABLE #recover673 (parentobject varchar(50), Object varchar(100), Field varchar(50), Value varchar(max))
GO
INSERT INTO #recover673 exec ('dbcc page(10,1,673,3) with tableresults')
GOCREATE TABLE #recover675 (parentobject varchar(50), Object varchar(100), Field varchar(50), Value varchar(max))
GO
INSERT INTO #recover675 exec ('dbcc page(10,1,675,3) with tableresults')
GOC’est la table d’offset de ligne qui permet de retourner les lignes dans l’ordre, dans les faits les lignes ne sont pas nécessairement classées physiquement dans la page. On se sert des slots pour connaître la dernière valeur de ‘a’ dans la page 1:673 et la première dans la page 1:675 (on rappelle qu’on n’a que deux lignes par page).
select Object, Value FROM #recover673 where Field='a'
Object Value
-------------------------------------- ---------
Slot 0 Column 0 Offset 0x4 Length 9 3
Slot 1 Column 0 Offset 0x4 Length 9 4
select Object, Value FROM #recover675 where Field='a'
Object Value
-------------------------------------- ---------
Slot 0 Column 0 Offset 0x4 Length 9 7
Slot 1 Column 0 Offset 0x4 Length 9 8Il s’agit donc des valeurs de clé 5 et 6 qui sont dans la page qui pose problème. On créé une table à l’identique et on injecte les deux plages de valeurs [0;4] et [7;1000]:
select * into T1_backup from T1 where 1=2
GO
insert into T1_backup select a,b from T1 with (index = IDX_T1C) where a in (1,2,3,4)
GO
insert into T1_backup select a,b from T1 with (index = IDX_T1C) where a >=7
GOEn vert, un hint qui permet de forcer un seek sur l’index clusterisé. En effet, un Clustered Index Scan aurait tenté de lire les pages 1:673 -> 1:674 et aurait remonté une erreur de chaînage et une erreur 824.
Il ne reste plus qu’à essayer de lire le contenu de la page endommagée pour voir ce qu’il peut y avoir de récupérable:
CREATE TABLE #recover674 (parentobject varchar(50), Object varchar(100), Field varchar(50), Value varchar(max))
GO
insert into #recover674 exec ('dbcc page(17,1,674,3) with tableresults')
GO
Si la page est lisible alors une requête simple permet de ramener les valeurs au format de T1:
with TA as (select cast(Object as char(6)) SlotID, Value from #recover674 where Field='a'), TB as (select cast(Object as char(6)) SlotID, Value from #recover674 where Field='b') insert into T1_backup select TA.Value as 'a', TB.value as 'b' from TA inner join TB on TA.SLotID = TB.SlotID a b ------------------------------- 5 bbbbbbbbbbbbbbbbbbb(...) 6 bbbbbbbbbbbbbbbbbbb(...)
Dans le cas où les données ne sont pas lisibles, on a au moins l’information de la plage qui est perdue, information que ne donne pas le repair_allow_data_loss.
A+. David B.
Continuez votre lecture sur le blog :
- Msg 2508, Level 16, State 1: the In-Row data %% for object %% is incorrect (David Baffaleuf) [SQL Server]
- Alter table rebuild (Benjamin VESAN) [SQL Server]
- Fragmentation sur des tables stockées en S-GAM (David Baffaleuf) [SQL Server]
- Production SQL Server : Contrôle de cohérence (Benjamin VESAN) [SQL Server]
- Intérêt de créer des indexes cluster uniques (David Baffaleuf) [SQL Server]