Une fois, on m'a demandé d'aider à «réparer» une base de données. CHECKDB lors de la vérification a donné une liste d'erreurs, dont certaines ont été marquées comme «incorrigibles». L'application a fonctionné, mais elle était encore quelque peu agitée.
Oui, la bonne solution dans une telle situation serait de faire une sauvegarde à partir du moment où les erreurs n'apparaissent toujours pas, de localiser les données corrompues et de les écraser à partir d'une copie propre. Mais ... comme cela arrive souvent, l'erreur a été détectée trop tard, il n'y avait donc vraiment rien à récupérer. D'un autre côté - il y aurait une sauvegarde, il n'y aurait pas cette histoire.
Anamnèse
La première étape consiste à lancer DBCC CHECKDB afin de comprendre l'ampleur de la tragédie. L'équipe a honnêtement parcouru toutes les tables, pour la plupart ne trouvant aucun problème. Dans la même conclusion, il y a eu cent rapports d'erreurs «corrigibles». Quelque chose comme ça:
Index row (1:386974:44) with values (C_FK_6bb5032ec2f94557a7d4a9d39a356168 = '04DA7FC4-B8F2-4D97-B8D2-B207A918D3DF' and C_PK_dd87d9cad5504a1199d4ddaf511ea6a9 = 'F44E8C34-862D-4952-8821-D0E87143A74E') pointing to the data row identified by (C_PK_dd87d9cad5504a1199d4ddaf511ea6a9 = 'F44E8C34-862D-4952-8821-D0E87143A74E').
Et quelques erreurs plus graves:
Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). Page (1:20426) is missing a reference from previous page (1:267203). Possible chain linkage problem.
Eh bien. L'échelle de travail est décrite, commençons!
Erreurs corrigibles
Afin de comprendre pourquoi certaines erreurs peuvent être facilement corrigées automatiquement, rappelons comment les index sont organisés dans MS SQL. Vous pouvez les diviser en 2 types: cluster et (étonnamment) non-cluster. (Nous ne nous pencherons pas sur des sujets particuliers tels que les indices columnstore - ce n'est pas le cas). Les deux sont un arbre équilibré, ce qui est très pratique pour trouver des données.
Il est important que les index de cluster à leur niveau «feuille» stockent directement le contenu des lignes de table. Mais les index non clusterisés stockent uniquement les données clés (et, si disponibles, les champs «inclus»), ainsi qu'un lien vers la ligne d'index de cluster. Autrement dit, si nous avons un problème dans un index non cluster, nous pouvons simplement prendre et écraser les données endommagées de l'index cluster. Eh bien, ou reconstruisez simplement l’index battu - c’est bien, la composition des champs est connue et les données source sont proches, intactes.
La tâche n'est absolument pas créative, vous pouvez donc la confier en toute sécurité à une voiture sans âme. Exécutez la commande
DBCC CHECKDB (< >, REPAIR_REBUILD)
et étudier le rapport d'étape.
Le journal source et le journal après le "correctif" contiennent les adresses des pages corrompues. Nous comparons ces adresses et nous assurons que toutes les erreurs marquées comme «corrigibles» ont bien été résolues.
Trouble de la connectivité des données
Maintenant quelque chose de plus grave. Après avoir corrigé les index non clusterisés et effacé le journal des messages d'information, le rapport contient trois enregistrements d'erreurs «fatales»
Vous pouvez, bien sûr, abandonner et couper avec la commande DBCC CHECKDB (<nom DB>, REPAIR_ALLOW_DATA_LOSS). Mais ... je ne veux tout simplement pas perdre les données. Je veux restaurer tout ce qui est possible au maximum. Par conséquent, voyons plus en détail ce que le rapport d'erreur nous dit en général.
Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). Page (1:20426) is missing a reference from previous page (1:267203). Possible chain linkage problem. Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). The previous link (1:267203) on page (1:267204) does not match the previous page (1:20426) that the parent (1:218898), slot 213 expects for this page. Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). B-tree chain linkage mismatch. (1:20426)->next = (1:267204), but (1:267204)->Prev = (1:267203).
Le rapport contient une histoire trouble qu'une page s'attend à voir un voisin, mais le voisin n'en sait rien. Certains marins pelevinsky Zheleznyak: sont allés sur le pont, mais pas de pont.
Pour une compréhension complète, plus de détails sont nécessaires et ils devront aller directement au contenu des pages. Mais avant cela, nous allons voir comment les pages d'index SQL Server sont liées en général.
De toute évidence, les pages ont des liens hiérarchiques «verticaux», qui forment l'arborescence B. La page supérieure contient des liens vers des pages d'un niveau inférieur et ainsi de suite jusqu'aux feuilles mêmes. Comme je l'ai déjà dit, cela est très pratique pour trouver des valeurs: vous voulez trouver «Vasya Pupkin» et après quelques pages («de B à G» → «de Ba à Bb» → «Vasya Pupkin»), vous trouvez celle souhaitée.
Mais il y a des situations où une requête doit sélectionner immédiatement des gammes entières de lignes ("De Vasya à Grisha"). Dans ce cas, chaque fois que vous descendez de l'arbre de haut en bas, vous vous y heurtez. Pour répondre à de telles demandes, les pages stockent des liens "horizontaux": chaque page connaît le numéro du voisin "avant" et "après". Avec ces relations, les analyses d'index sont beaucoup plus faciles à effectuer.
À en juger par le journal des erreurs, nous avons eu une inadéquation des connexions horizontales et verticales. Mais pour en être finalement convaincu, regardons les pages elles-mêmes.
Nous devons aller plus loin!
Pour consulter les pages, nous utiliserons l'ancienne équipe DBCC PAGE, bien méritée et non documentée. Il prend 4 paramètres:
- Eid Base
- Identifiant du fichier de base
- Identifiant de la page
- Niveau de détail (0 à 3)
En fonction du dernier paramètre, vous pouvez voir soit uniquement l'en-tête de service (0), soit l'intégralité du contenu de la page (3), soit l'en-tête et une partie du contenu (1 et 2)
Soit dit en passant, dans SQL Server 2019, une vue documentée
sys.db_db_page_info est enfin apparue, qui effectue des tâches similaires. Malheureusement, il ne montre que les données d'en-tête (analogiques au drilldown 0), donc il ne répond toujours pas complètement à nos tâches.
Donc, pour commencer, exécutez la commande
DBCC TRACEON (3604, 1)
de sorte que la sortie des commandes DBCC restantes arrive à notre console, et non à ErrorLog
Après cela, regardez le titre de la page 20426:
DBCC PAGE (11, 1, 20426, 0)
Je vois. La page veut être au milieu entre les pages 267203 et 267204. Mais qu'en est-il de ces mêmes pages?
Pas de sommeil, pas d'esprit! Ils sont bien sans invités non invités.
Eh bien, regardons la table des matières à venir:
Le puzzle se développe progressivement:
- En termes de liens «verticaux» (arborescence d'index), la page 20426 doit être comprise entre 267203 et 267204
- Les connexions horizontales contredisent cela et disent qu'il n'y a personne entre 267203 et 267204.
Essayons maintenant de comprendre quel type de données a souffert de l'échec.
Les lignes de la page sont triées par clé d'index. Par conséquent, en connaissant la première et la dernière valeur de clé sur la page, vous pouvez trouver la plage d'enregistrements «affectés».
La clé peut être consultée simplement dans la description de l'index. Nous utiliserons la commande
sp_helpindex pour cela. Dans ce cas, la clé est une seule colonne, incl. fais attention à elle.
Afin de trouver les clés qui se trouvent sur la page, nous utilisons DBCC PAGE avec un maximum de 3 niveaux de détails. Si vous faites défiler le journal de sortie, vous pouvez voir le contenu "brut" de chaque ligne et une ventilation pour chaque champ:
Par ailleurs, les valeurs de limite de clé (la première valeur sur la page) peuvent également être vues dans la sortie PAGE DBCC pour la page d'index supérieure (218898, voir la capture d'écran ci-dessus). Ils sont affichés dans la colonne qui suit immédiatement le numéro de page.
Un examen des pages montre que la page 20426 contient exactement la moitié des lignes de la page 267203. La nature de l'échec devient claire. Lorsque la page de la base de données est pleine et qu'elle n'a plus d'espace pour insérer de nouvelles données, elle est divisée en deux en 2 nouvelles pages. Apparemment, lorsque la page 267203 est devenue pleine, la page de problème 20426 a été créée. Le SGBD a commencé à reconstruire ses liens: il a réussi à écrire une nouvelle page dans la structure d'index. Mais pour une raison quelconque, les enregistrements horizontaux n'ont pas pu être mis à jour et la nouvelle page s'est «accrochée» dans un état indéfini.
Qu'est-ce que tout cela sera pour nous?
Il n'y a certainement pas grand-chose de bon. Des liens de page sont évidemment nécessaires pour accéder aux données. Lors de l'exécution de la requête, le SGBD détermine indépendamment le chemin d'accès à ces mêmes données. Mais la règle générale (à partir de laquelle des exceptions sont possibles) est la suivante:
- Les sélections «larges» (par exemple, pour les rapports) sont effectuées à l'aide d'enregistrements horizontaux. Il est plus facile de faire défiler plusieurs pages séquentiellement, en choisissant une large plage
- Les requêtes «ponctuelles» (mise à jour d'un enregistrement spécifique) sont effectuées par une recherche «table des matières».
Il s'avère que lorsque des enregistrements spécifiques changent, le SGBD accède au «problème» page 20426. Et lorsqu'il exécute le rapport, il lit les données «horizontalement» et ne voit pas les modifications apportées. Encore une fois: en pratique, l'algorithme peut être plus compliqué, mais la classe des problèmes possibles est toujours compréhensible.
Les questions éternelles de l'intellectuel russe
Pour être honnête, je ne vois toujours pas comment cela pourrait se produire. Un SGBD moderne est en fait une chose assez fiable. Toutes les modifications du fichier de base de données (y compris l'ajustement des relations horizontales et verticales) sont effectuées dans une transaction. Ces opérations sont enregistrées dans le journal des transactions et, s'il n'y a aucune confirmation d'une opération réussie dans ce journal, toutes les opérations sont annulées. Ici, vous pouvez voir que la transaction s'est terminée avec succès, mais certaines des modifications le long du chemin d'accès au fichier de données ont été «perdues».
La seule explication plausible qui m'est venue à l'esprit était une panne du cache du sous-système de disque. Toutes les données sont entrées dans le cache, puis une partie des enregistrements du fichier de données et du journal ont été écrits sur le disque - puis quelqu'un a tiré le commutateur. En conséquence, les enregistrements «horizontaux» modifiés n'ont pas pu être enregistrés, mais la base de données n'en savait déjà rien. (Et ici, de nombreux lecteurs devraient se précipiter pour vérifier les batteries de leurs contrôleurs de disques industriels)
Que faire est beaucoup plus clair. Pour restaurer la connectivité d'une table, il suffit de copier ses données dans une autre table pour que la lecture horizontale soit utilisée lors de la copie. Pour la fidélité, vous pouvez spécifier explicitement la bonne méthode d'accès au SGBD à l'aide de l'indication FORCESCAN
select * into T_bca79e9e77c24cdc8bbb7cfd0ddc16fd_BKP from T_bca79e9e77c24cdc8bbb7cfd0ddc16fd with (FORCESCAN)
Après cela, vous pouvez effacer la table source et y renvoyer les données copiées.
Mais les modifications apportées à la page 20426 seront perdues.
Par conséquent, avant de faire une copie du tableau, vous devez copier les lignes avec les identificateurs de la page 20426. Et après avoir restauré le tableau, corrigez les entrées nécessaires. L'accès aux enregistrements de la page 20426 est explicitement nécessaire par des identifiants:
select * from T_bca79e9e77c24cdc8bbb7cfd0ddc16fd where C_PK_bca79e9e77c24cdc8bbb7cfd0ddc16fd = ''
Les identifiants peuvent être obtenus en lisant le contenu de la page avec la même PAGE DBCC. Comme je l'ai déjà écrit, il y avait exactement la moitié de ces pages 267203, soit 15 liens.
Sauvegardez les pages endommagées, rechargez la table et faites correspondre les enregistrements correspondants - après quelques dizaines de minutes, la table a été restaurée.
Hourra, nous avons gagné! Mais est-ce vrai?
La vérité est. Les données ont été restaurées, CHECKDB a cessé de verser des erreurs, même le soleil a regardé par la fenêtre. Vous pouvez vous vanter en toute sécurité, lever un verre bien mérité d'une boisson de hussard et ... rappelez-vous que les données de la base de données ne sont pas uniquement liées à des liens vers des pages. Il est donc temps de prendre
DBCC CHECKCONSTRAINTS et de plonger tête baissée dans la liste des clés étrangères cassées. Mais c'est une histoire complètement différente ...
Quoi d'autre à lire sur le sujet
- Syntaxe de commande CHECKDB (Faites attention aux avertissements sur les risques possibles d'utilisation de la commande!)
- La description non officielle de DBCC PAGE
- Un bon article sur les index MS SQL qui explique beaucoup de choses intéressantes. Y compris la façon dont les index physiques sont stockés dans la base de données