SQL Server vu par Christian Robert

SQL Server : Maintenance courante, vérifiez l’intégrité de la base de données avec DBCC CHECKDB

Si vous ne le faites pas encore c'est le moment de planifier cette opération régulièrement sur vos bases de données. A l'instar de CHKDSK sur les disques de vos machines, les fichiers de bases de données et leur structure internes peuvent subir des dommages.

Dans la série des problèmes pouvant survenir et qui peuvent être détectés par DBCC CHECKDB :

• Problème de corruption logique ou physique
  • A noter que SQL Server lorsqu'il rencontre ce type de problèmes à l'écriture remonte une erreur (et dans certains cas à la lecture). DBCC CHECKDB permet une vérification globale sur le CHECKSUM est présents sur chaque page
  • http://blogs.codes-sources.com/christian/archive/2009/12/11/sql-server-ai-je-des-donn-es-corrompus-dans-ma-base-de-donn-es-msdb-dbo-suspect-pages.aspx
• Vérification de l'intégrité des métas donnés
  • En vérifie le contenu des en-têtes de pages, les chaînages d'index, etc.
• Vérification des vues indexées, index géo-spatiaux et index XML avec WITH EXTENDED_LOGICAL_CHECKS
  • La vérification de ces index spéciaux est assez couteuse, c'est pour cela qu'une option a été crée sous SQL Server 2008 pour choisir ou non de vérifier leur intégrité. Sous SQL Server 2005 la vérification des vues indexées et index XML etait systématique par défaut.
• Vérification de certains types (float, datetime) avec WITH DATA_PURITY
  • Suite à migration depuis SQL Server 2000

Elle s'exécute facilement, mais nécessite comme toutes les commandes DBCC des privilèges de sysadmin sur l'instance.

DBCC CHECKDB

-- base de données courante

DBCC CHECKDB(5)

-- où 5 correspond à un numéro d'une base de données --> sys.databases

Ou en boucle sur toutes les bases de données d'une même instance (eh oui un curseur) :

DECLARE @dbid int ;

DECLARE crsDB CURSOR FOR SELECT database_id FROM sys.databases FOR READ ONLY ;

OPEN crsDB ;

FETCH NEXT FROM crsDB INTO @dbid ;

WHILE ( @@FETCH_STATUS = 0)

BEGIN

    DBCC CHECKDB( @dbid ) WITH NO_INFOMSGS ;

    FETCH NEXT FROM crsDB INTO @dbid ;

END

CLOSE crsDB ;

DEALLOCATE crsDB ;

A noter aussi l'existence d'une tâche dans les plans de maintenance, intitulé « vérification de l'intégrité de la base de données ».

Cette commande permet aussi de réparer une partie des défauts détectés durant le contrôle.

En pratique, la commande DBCC CHECKDB génère un Snapshot (instantané) de la base de données sur laquelle le contrôle d'intégrité a été demandé, et cela quelque soit l'édition de SQL Server sur laquelle vous tournez. Celui-ci va consommer un peu d'espace disque en fonction des écritures en cours sur la base de données source. Cet instantané est invisible et utilise les emplacements des fichiers sources, vérifiez bien qu'assez d'espace est disponible avant l'utilisation de DBCC CHECKDB et éviter son utilisation concomitante avec une maintenance d'index ou tout autre tâche exigeant beaucoup d'écritures au sein de la base de données.

Quand exécuter cette commande ?

• Avant chaque sauvegarde complète
  • Certes cela ne vous offre qu'une garantie limitée, mais il est probable à plus de 95% que le contenu de la sauvegarde soit ok
• A chaque redémarrage inattendu de votre instance, ou redémarrage forcé (on redémarre Windows ou on coupe l'instance sans attendre le COMMIT des transactions en cours)
  • Plus par précaution qu'autre chose, si le système disque est préservé par batterie il ne devrait pas y avoir de problèmes
• Après chaque mise à niveau de SQL Server
  • Passage de 2000 à 2005 ou 2005 à 2008… En option à chaque Service pack.
• Lors d'une période d'activité calme et surtout pas en même temps qu'une autre tâche de maintenance de type sauvegarde ou optimisation des index !

Quelques astuces :

• WITH NO_INFOMSGS
  • Cette clause vous permets de supprimer les centaines de messages, vous signalant que l'analyse de la table x ou de l'index y est en cours et de fait ne renvoie que les erreurs trouvées
• La colonne percent_complete de la vue dynamique sys.dm_exec_requests vous permet de suivre l'avancement de votre vérification d'intégrité
• Si la vérification de l'intégrité n'est pas possible sur votre système (impact trop important du snapshot, trop peu d'espace libre, etc.). Dans ces cas, faites une sauvegarde complète de votre base de données, restaurez là sur une autre instance et exécutez le DBCC CHECKDB sur cette nouvelle base de données.
• Si vous souhaitez découper vos vérifications de bases de données, les commandes suivantes sont des alternatives au DBCC CHECKDB (voir http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx le gars qui a écrite une partie des commandes DBCC, pour plus d'infos)
  • Par groupe de fichier (FILEGROUP)
    • DBCC CHECKFILEGROUP
  • Table par table et en découpant par types de contrôle
    • DBCC CHECKALLOC
    • DBCC CHECKCATALOG
    • DBCC CHECKTABLE

Donc, commande indispensable, à utiliser régulièrement pour préserver la santé de vos bases de données.

Bonne vérification…