By : Brady Upton | Mis à jour : 2016-07-26 | Commentaires (2) | Connexe : Plus de > Commandes de la console de base de données DBCC

Problème

La corruption de la base de données de SQL Server peut être un problème et peut causer de graves dommages à une base de données. Si vous êtes un DBA expérimenté, alors vous avez probablement des sauvegardes en place pour détecter cela, mais au fil des ans, j’ai vu des centaines de serveurs SQL sans aucune méthode de détection et c’est un problème. Il existe quelques moyens de détecter la corruption de la base de données, mais cette astuce se concentrera davantage sur DBCC CHECKDB.

Solution

Vous pouvez ou non avoir entendu parler des déclarationsDBCC (commandes de la console de la base de données). Ces instructions sont utilisées pour effectuerdifférentes opérations dans votre base de données et peuvent être réparties en quatre catégories :Maintenance, Divers, Informationnel et Validation. J’utilise quotidiennement certaines des déclarations DBCC, mais aucune plus que DBCC CHECKDB.

Qu’est-ce que SQL Server DBCC CHECKDB

DB CHECKDB, deMicrosoft MSDN Library, vérifie l’intégrité logique et physique de tous les objets de la base de données spécifiée en effectuant les opérations suivantes :

  • Exécute DBCC CHECKALLOC sur la base de données – Vérifie la cohérence des structures d’allocation d’espace disque pour une base de données spécifiée.
  • Exécute DBCC CHECKTABLE sur chaque table et vue de la base de données – Vérifie l’intégrité de toutes les pages et structures qui composent la table ou la vue indexée.
  • Exécute DBCC CHECKCATALOG sur la base de données – Vérifie la cohérence du catalogue dans la base de données.
  • Valide le contenu de chaque vue indexée dans la base de données.
  • Valide la cohérence au niveau des liens entre les métadonnées de la table et les répertoires et fichiers du système de fichiers lors du stockage de données varbinaires(max) dans le système de fichiers en utilisantFILESTREAM.
  • Valide les données du Service Broker dans la base de données

Si vous avez déjà exécuté DBCC CHECKDB, vous savez que cela prend un certain temps pour les grandes bases de données.Maintenant que vous connaissez toutes les étapes qui sont exécutées, vous pouvez voir pourquoi cela prend du temps pour se terminer.

Comment SQL Server DBCC CHECKDB peut-il m’aider ?

La corruption des données est mauvaise. Elle peut causer toutes sortes de problèmes au sein de la base de données, notamment des résultats de données incorrects, des instructions SQL échouées et, dans certains cas, peut mettre hors service l’instance SQL entière. DBCC CHECKDB vous avertit de la corruption afin que vous puissiez la réparer avant (avec un peu de chance) qu’elle ne devienne trop grave.

Comment utiliser DBCC CHECKDB de SQL Server ?

DBCC CHECKDB est assez simple. Il y a quelques options que vous pouvez utiliser avec l’instruction et nous en aborderons certaines dans la section suivante, mais la syntaxe de base ressemble à ceci :

DBCC CHECKDB ('DatabaseName')

Pretty simple.

Automatiser SQL Server DBCC CHECKDB

De toute évidence, vous ne voulez pas vous connecter tous les matins et exécuter cette déclaration sur chaque base de données, vous pouvez donc automatiser ce processus en utilisant quelques méthodes différentes:

  • Plans de maintenance SQL Server -Les plans de maintenance font partie de SQL Server dès la sortie de la boîte (sauf si vous exécutez ExpressEdition). Je n’aime pas utiliser les plans de maintenance pour la plupart, mais je n’ai pas peur de les utiliser pour ce type de tâche. Dans la boîte à outils du plan de maintenance, vous devrez utiliser la tâche Vérifier l’intégrité de la base de données. La seule option configurable est d’inclure les index, donc ce n’est pas vraiment convivial, mais dans certains cas, c’est tout ce dont vous avez besoin. Encore une fois, nous parlerons des autres options dans la prochaine section.
  • Scripts personnalisés – Les scripts personnalisés sont généralement ce que j’utilise et offrent la meilleure flexibilité en ce qui concerne l’ajout des options que vous voulez. Mes go-toscripts sont déjà créés et peuvent être utilisés gratuitement par Ola Hallengren. Il a fait un travail formidable en les créant et en les partageant avec le monde entier. MerciOla!
    • Voyez les scripts sur MSSQLTips.com:
    • Exécuter une maintenance avec des bases de données SQL Server en mode de récupération complète
    • Plans de maintenance des bases de données SQL Server et gestion des fichiers de sauvegarde
    • Exécuter une maintenance SQL Server sans fenêtre de maintenance

    Options DBCC CHECKDB de SQL Server

    Il existe quelques options à utiliser avec DBCC CHECKDB et je vais passer en revue quelques-unes des plus populaires ici :

    • NOINDEX – Spécifie que les vérifications intensives des index non groupés pour les tables utilisateur ne doivent pas être effectuées. Cela diminue le temps d’exécution de l’overallex. NOINDEX n’affecte pas les tables système car les vérifications d’intégrité sont toujours effectuées sur les index des tables système.
    • NO_INFOMSGS – Supprime tous les messages d’information.
    • PHYSICAL_ONLY – Limite la vérification à l’intégrité de la structure physique des en-têtes de page et d’enregistrement et à la cohérence d’allocation de la base de données. Cette vérification est conçue pour fournir un petit contrôle de surcharge de la cohérence physique de la base de données, mais elle peut également détecter les pages déchirées, les échecs de somme de contrôle et les défaillances matérielles courantes qui peuvent compromettre les données d’un utilisateur.
    • TABLOCK – Fait en sorte que DBCC CHECKDB obtienne des verrous au lieu d’utiliser un instantané interne de la base de données. Cela inclut un verrou exclusif (X) à court terme sur la base de données. TABLOCK permet à DBCC CHECKDB de s’exécuter plus rapidement sur une base de données soumise à une forte charge, mais diminue la concurrence disponible sur la base de données pendant l’exécution de DBCC CHECKDB.
    • DATA_PURITY – Demande à DBCC CHECKDB de vérifier dans la base de données les valeurs de colonnes qui ne sont pas valides ou hors limites. Par exemple, DBCC CHECKDBdétecte les colonnes dont les valeurs de date et d’heure sont supérieures ou inférieures à la plage acceptable pour le type de données datetime ; ou les colonnes de type de données décimales ou approximatives-numériques dont les valeurs d’échelle ou de précision ne sont pas valides.

    Nous passerons en revue certaines des options de RÉPARATION dans une autre section ci-dessous.

    À quelle fréquence dois-je vérifier la corruption de SQL Server ?

    A chaque minute de chaque jour. Je plaisante.

    Si vous avez une fenêtre de maintenance quotidienne, ce serait bien de vérifier la corruption des données quotidiennement. Plus vite vous pouvez l’attraper, moins il y a de dégâts. J’ai remarqué que beaucoup de gens exécutent cela le week-end, surtout avec les grandes bases de données. Il n’y a pas de bien ou de mal à cela, assurez-vous simplement que vous l’avez programmé périodiquement.

    Dois-je exécuter DBCC CHECKDB dans mon environnement de production ?

    Non, enfin oui. En quelque sorte.

    Pour vérifier la corruption des données, il n’est pas utile d’exécuter DBCC CHECKDB sur votre environnement de test..A MOINS QUE vous ne restauriez une copie de votre environnement de production pour tester et ensuite l’exécuter. BRILLIANT!

    Vous vous demandez peut-être si certaines options HA sont adaptées, telles que AlwaysOn, LogShipping, etc. Non, vous devez vérifier votre environnement de production *live*.

    Maintenant que DBCC CHECKDB est configuré et en cours d’exécution, que dois-je chercher ?

    Félicitations ! Vous avez automatisé et exécuté DBCC CHECKDB, mais maintenant quoi ? Si vous avez unSQL ServerAgent Job configuré alors assurez-vous de configurerDatabase Mail,un opérateur et une notification sur le job. Si le travail réussit, continuez votre belle journée. Si le travail échoue, alors nous avons du travail à faire.

    Vous pouvez voir une erreur comme celle-ci:

    The In-row data USED page count for object « tablename », index ID 2, partitionID 608313809829888, alloc unit ID 608313809829888 (type In-row data) is incorrect.Run DBCC UPDATEUSAGE. (Erreur 2508) Les données In-row RSVD pagecount pour l’objet « tablename », index ID 2, par…. L’étape a échoué.

    ou ceci:

    Objet ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unitID 72345201051571606 (type In-row data) : La page (1:94299) n’a pas pu être traitée. Voir les autres erreurs pour plus de détails. Msg 8939, Level 16, State 98, Line 1 Erreur de table : Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unitID 72345201051571606 (type In-row data), page (1:94299). Le test (IS_OFF (BUF_IOERR,pBUF->bstat)) a échoué. CHECKDB a trouvé 0 erreur d’allocation et 2 erreurs de cohérence dans la table ‘tablename’ (ID objet 2088535921). CHECKDB a trouvé 0 erreur d’allocation et 2 erreurs de cohérence dans la base de données ‘tablename’. repair_allow_data_lossest le niveau de réparation minimum pour les erreurs trouvées par DBCC CHECKDB (Database).

    Premièrement, ne paniquez pas. Deuxièmement, vérifiez les sauvegardes. Les erreurs DBCC CHECKDB vous indiquent généralement ce qui doit être fait.

    Pour la première erreur, un DBCC UPDATEUSAGE corrigera les inexactitudes du nombre de pages et de lignes dans les vues du catalogue. Plutôt inoffensif.

    La deuxième erreur signale une corruption de données. L’erreur mentionne l’utilisation de repair_allow_data_lossas comme niveau de réparation minimum. Cela signifie que vous pouvez exécuter l’instruction avec cet argument,mais vous risquez de perdre des données. C’est pourquoi je recommande toujours de restaurer sur une sauvegarde si vous le pouvez. Vous devez vous assurer que la sauvegarde ne contient pas de données corrompues et vous voulez vous assurer qu’il n’y a pas de perte de données.

    Comment réparer une base de données SQL Server

    Si vous n’avez pas de sauvegarde, nous pouvons avoir besoin d’utiliser DBCC CHECKDB avec une option de réparation.Voici les options de réparation qui sont disponibles à utiliser. Celles-ci peuvent ou non fonctionneret doivent être utilisées en dernier recours :

    • REPAIR_ALLOW_DATA_LOSS – Tente de réparer toutes les erreurs signalées.Ces réparations peuvent entraîner une certaine perte de données.
    • REPAIR_REBUILD – Effectue des réparations qui n’ont aucune possibilitéde perte de données. Cela peut inclure des réparations rapides, telles que la réparation des lignes manquantes dans les index non groupés, et des réparations plus longues, telles que la reconstruction d’un index.

    Comme je l’ai dit plus haut, il est très important d’avoir des sauvegardes à jour pour récupérer une corruption. La corruption ne se soucie pas de la quantité de données que vous avez, de la version de SQL que vous exécutez ou de la fantaisie de votre centre de données.

    Je me sens plutôt bien à ce sujet, mais qu’est-ce que je peux utiliser d’autre pour protéger mes instances SQL ?

    DBCC CHECKDB devrait être exécuté sur chaque instance SQL que vous avez, mais il y a quelques autres moyens qui peuvent aider à détecter/prévenir la corruption des données.

    • Alertes de l’agent SQL Server – Brian Kelley a écrit un bon conseil sur ce sujet ici.
    • Vérification des pages – Assurez-vous que vos bases de données utilisent la vérification des pagesCHECKSUM. Si vous exécutez encore SQL 2005 ou inférieur, ce n’est pas disponible, mais après la mise à niveau, assurez-vous de modifier ce paramètre. Pour voir les paramètres de vérification des pages, utilisez l’instruction suivante:
    select name, page_verify_option_desc from sys.databases
    Next Steps
    • Vérifiez la bibliothèqueMSDN pour des détails plus approfondis concernant DBCC CHECKDB
    • MSSQLTips.com a une belle collection de conseils sur les contrôles de cohérence des bases de données qui peuvent également être utiles.

    Dernière mise à jour : 2016-07-26

    A propos de l’auteur
    Brady Upton est un administrateur de bases de données et une superstar de SharePoint à Nashville, TN.
    Voir tous mes conseils
    Ressources connexes

    • Plus de conseils DBA SQL Server…

By: adminPosted on

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.