La plupart du temps, lorsqu’on pose la question à des personnes qui débutent sous SQL Server, la réponse à la question : « comment faites-vous pour résoudre une erreur de page déchirée ou de somme de contrôle « , la réponse est bien souvent : « j’utilise la commande DBCC CHECKDB avec l’option REPAIR_ALLOW_DATA_LOSS » …

Et là, c’est le drame, car il existe avant cette options bien d’autres solutions.

Comme vous allez le voir, l’option REPAIR_ALLOW_DATA_LOSS est une option à utiliser en dernier recours, tellement les dégâts qu’elle peut causer sont dangereux …

Comment, dès lors, utiliser la commande DBCC CHECKDB, dont l’utilité n’est ici pas remise en cause, bien au contraire !
Si vous avez choisi la bonne stratégie de sauvegarde suivant le métier qui fait vivre la base de données, vous devez en plus effectuer, au pire par abus de conscience, un contrôle d’intégrité, à une fréquence adaptée elle aussi au métier de l’entreprise.
Pour une base de données utilisée qui doit être hautement disponible, on peut préférer par exemple vérifier l’intégrité de celle-ci tous les jours à l’aide de l’instruction suivante :

DBCC CHECKDB ('maBD') WITH NO_INFOMSGS

L’option WITH NO_INFOMSGS ne montre pas tous les messages d’information qui sont normalement retournées sans cette option (nombre de pages vérifiées par table, …).
En revanche, on verra toujours les erreurs apparaître.
Il sera donc pratique de faire exécuter cela par un job à une heure de faible activité sur la base de données, et d’adjoindre au job un fichier de sortie pour pouvoir consulter a posteriori ce fichier.

Comme cette option peut consommer une quantité de ressources système élevée, on peut tout à fait se rabattre sur :

DBCC CHECKDB ('maBD') WITH PHYSICAL_ONLY, NO_INFOMSGS

Cette instruction permet de ne vérifier les structures sur disque, sans scruter les structures logiques internes aux pages de la base de données.

Revenons à nos moutons : nous avons détecté un problème d’intégrité dans la base de données, et maintenant, que fait-on ?

Si, comme nous l’avons vu un peu plus haut, nous avons choisi la bonne stratégie de sauvegarde, alors nous sommes sauvés.
Restaurer une base de données corrompue est de loin la meilleure solution à employer pour passer outre une corruption de données, mais il existe encore, avant de prendre cette décision qui peut être lourde de conséquences dans certains contextes, bien d’autres possibilités.
Voyons à quoi ressemble une erreur de corruption de page :

Msg 8928, Level 16, State 1, Line 1
Object ID 2078435228, index ID 0, partition ID 72342401522583797, alloc unit ID 72385201253561693 (type In-row data): Page (1:62418) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2078435228, index ID 0, partition ID 72342401522583797, alloc unit ID 72385201253561693 (type In-row data), page (1:62418). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘elsuket’ (object ID 2078435228).
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘elsuket’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ELSUKET).

Comme on le voit, la page corrompue nous est précisée.
Il serait avantageux de savoir à quel objet appartient cette page, car :

– si c’est un index non-cluster, il suffit de reconstruire celui-ci (ce n’est pas le cas avec un index cluster)
– si c’est un autre type d’objet, nous pouvons utiliser l’option PAGE de l’instruction RESTORE DATABASE.

Notons que cette dernière possibilité n’est possible que dans le mode de restauration FULL.
Examinons maintenant la page qui est corrompue.
Malheureusement cela nécessite de connaître des drapeaux de trace et instructions qui ne sont pas documentés :

Le drapeau de trace 3604 est utilisé ici pour remonter les traces d’exécution de toute commande DBCC au client de la session de l’utilisateur.

La commande DBCC PAGE est ici spécifiée comme suit :

– ‘ELSUKET’ est le nom de la base de données,
– 1 est le numéro de fichier (c’est le même numéro que celui qui précède le numéro de page dans le résultat de la commande DBCC CHECKDB )
Р62418 est le num̩ro de page
– 3 est l’option de suivi de scrutation des pages : on aura l’en-tête de buffer, l’en-tête de page, chaque ligne de données que stocke la page, la table des offsets de lignes.

Dans le listing, nous trouverons des repères nommés MetaData, et nous pourrons trouver, par exemple :

C’est plus précisément Metadata: IndexId qui nous intéresse ici :

– S’il est plus grand que 1, il s’agit d’une index non-cluster, donc il suffit de le supprimer et de la reconstruire
– S’il vaut 0 ou 1, nous devons résoudre le défaut d’intégrité de façon plus complexe.

=> Restauration de pages à partir d’une sauvegarde de la base de données

On peut tout simplement restaurer la page concernée à partir de notre sauvegarde, en ayant au préalable sauvegardé la queue du fichier du journal des transactions, c’est à dire le journal des transactions courant.
Il faut alors restaurer la dernière sauvegarde complète, puis l’éventuelle dernière sauvegarde différentielle, puis les journaux de transaction dans l’ordre où ils ont été sauvegardés, pour enfin restaurer la queue de celui-ci, mais avec l’option NORECOVERY de l’instruction RESTORE.

Si peu de pages sont corrompues, on peut alors décider de les restaurer une à une à partir de la base de données fraîchement restaurée :

Rappelons que cela n’est possible que dans le mode de restauration FULL.
Si la base de données utilise le mode de restauration SIMPLE, alors on devra restaurer la base de données à partir de la dernière sauvegarde complète et des éventuelles sauvegardes différentielles, mais on perdra les données qui ont été ajoutées ou modifiées entre l’heure de terminaison des sauvegardes et maintenant.

=> La réparation automatique, et ses dangers

Il est important, avant de choisir parmi les options de réparation de corruption, l’option qui convient :

– REPAIR_ALLOW_DATA_LOSS
– REPAIR_REBUILD

Si nous revenons à la trace produite par l’exécution de la commande DBCC CHECKDB, on trouve dans celle-ci le niveau de correction à adopter :

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

Si l’option proposée est REPAIR_REBUILD, vous l’avez échappé belle : il n’y aucun risque de perte de données : il s’agira pour le moteur de base de données de réparer des clés manquantes dans les index non-cluster, ou bien de reconstruire un index.

Si en revanche vous avez, comme ici, l’option REPAIR_ALLOW_DATA_LOSS, comme le dit si bien cette options, il est possible que vous perdiez des données (d’où l’importance des sauvegardes).
En effet, cette commande peut par exemple supprimer la page et la ré-allouer pour faire « croire » que celle-ci n’a en fait jamais existé…

En conclusion : bichonnez vos sauvegardes !

Pour aller plus loin, je vous propose cet article de Mikedavem : La vérification d’intégrité et les problématiques liées aux VLDB