Discussion :

[Michel.Priori]

Bonjour à tous,

Je recontre beaucoup de situation au seins desquelles j'ai l'impression que le "pouvoir" du DBCC CHECKDB reste un chose mystérieuse.
Il y a les "adorateurs", qui lancent la commande un peu n'importe quand, l'incluent dans les chaines d'ingestion de données.
Il y a les "détracteurs". Ce sont en général ceux qui sont proches de l'importance de la "PROD" et de sa disponibilité opérationnelle.
Il y a les "inconscients", qui ne savent pas que cette commande existe.
Je pense qu'il faille remettre l'église au centre du village.

Le problème est le combo temps-impact de la commande DBCC CHECKDB sur les bases de prod, les VLDB en particulier.
Donc pour moi cela se traduit par : "quelle est la stratégie minimale incluant une prise de risque rationnelle ?"

Le soucis est que la BOL n'est pas très compréhensible ; La distinction de sens de ces 2 phrases vous est évidente ?

• Vérifie l'intégrité de toute les pages et structures qui composent la table ou la vue indexée
• Vérifie la cohérence des structures d'allocation de l'espace disque pour une base de données spécifiée.

La BOL : https://learn.microsoft.com/fr-fr/sq...l-server-ver16
Une note d'attention :

Cela signifie que les commandes DBCC CHECKALLOC, DBCC CHECKTABLE et DBCC CHECKCATALOG ne doivent pas être exécutéesséparément de DBCC CHECKDB.

Il est déconseillé d'executer les commandes sous jacentes unitairement, car certains contrôles ne seraient pas fait.
les Bonnes pratiques

Nous vous recommandons d'utiliser l'option PHYSICAL_ONLY pour une utilisation fréquente sur des systèmes de production. L’utilisation de PHYSICAL_ONLY permet de raccourcir fortement le temps d’exécution de DBCC CHECKDB sur les grandes bases de données. Nous vous recommandons aussi d’exécuter régulièrement DBCC CHECKDB sans options. La fréquence à laquelle vous devez effectuer ces exécutions dépend de chaque activité et de son environnement de production.

Les options de la commandes permettent de :
Rédéfinir le volume à traiter (NOINDEX,EXTENDED_LOGICAL_CHECKS,PHYSICAL_ONLY,DATA_PURITY)
Réparer (REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD)
Informer (ALL_ERRORMSGS, NO_INFOMSGS
Agir sur le comportement (TABLOCK,MAXDOP)
Prévoir l'impact sur la tempDB (ESTIMATEONLY)

Réparer les problèmes ne se planifient pas !
Du coup cela implique qu'on peut se passer de planifier les contrôles de ce qu'on peut réparer ; la probilité de la panne et faible et on a une remédiation.

Si on est en Always On, ou en log shipping, le support de stockage devrait être distinct (!)
Du coup la probabilté d'incident physico-logique sur les 2 instances deviennent très faible.
On imagine pouvboir réparer l'un avec l'autre (dans le cas de AO, la réparation des pages défaillante est automatique)

Du coup on fait quoi ?

• on associe le CHECKDB aux seules opérations de backup (ou de restore ?) ?
• on utilise PHYSICAL_ONLY toutes les heures ? et sans option tous les jours ?

Merci de vos retours éclairés

[SQLpro]

Pour une VLDB (je le considère dès qu'une base dépasse les 2 To) il est essentiel de découper le stockage en différents groupe de fichiers :
1) le groupe PRIMARY ne devrait jamais contenir de tables de production ni d'index. Il ne contiendra donc que les métadonnées (tables système).
2) un groupe spécifique doit être créé pour les tables de production vivantes.
3) un groupe spécifique doit être créé pour les LOBS, et il faut utiliser la directive TEXTIMAGE_ON au moment de la création des tables.
4) différents groupes de fichiers doivent être créés pour les données tièdes et froides.

À partir de ce moment... Il est essentiel de ne pas faire un DBCC CHECKDB, mais de procéder par groupe de fichiers.

La fréquence de l'exécution des DBCC CHECK dépend des sauvegardes. Je considère que cette fréquence doit être la même que la sauvegarde FULL, car les possibilité de récupération des données abimées se reposent sur les sauvegardes. Elle dépend aussi du contenu des groupe de fichiers. Pour les groupe de fichiers "froid" (archive pas exemple) les placer en READONLY et faire une sauvegarde. Dès lors un seul DBCC CHECKFILEGROUP suffit, juste après la sauvegarde...

Lisez attentivement le document que j'ai écrit concernant la récupération des données abimées
https://blog.developpez.com/sqlpro/p...ver-corrompues

Un des moyens de récupération les plus intéressant est la restauration de page qui peut se faire à chaud en version Enterprise.

A +

[Michel.Priori]

Pour une VLDB (je le considère dès qu'une base dépasse les 2 To) il est essentiel de découper le stockage en différents groupe de fichiers :
1) le groupe PRIMARY ne devrait jamais contenir de tables de production ni d'index. Il ne contiendra donc que les métadonnées (tables système).
2) un groupe spécifique doit être créé pour les tables de production vivantes.
3) un groupe spécifique doit être créé pour les LOBS, et il faut utiliser la directive TEXTIMAGE_ON au moment de la création des tables.
4) différents groupes de fichiers doivent être créés pour les données tièdes et froides.

À partir de ce moment... Il est essentiel de ne pas faire un DBCC CHECKDB, mais de procéder par groupe de fichiers.

Note : Pour moi la définition d'une VLDB dépend de la capacité de traitement du serveur associé ; dès que les opérations de maintenance posent un problème de planification, je considère qu'on n'est plus dans une base de données standard, que l'on est, peu ou prou, en face d'une VLDB.

Ca ne change pas l'essence de la réponse : segmenter le stockage et réflechir par groupe de fichier ; merci du rappel
En plus la segmentation proposée est compatible avec une stratégie de restauration "réparatrice" adossée à une stratégie de sauvegarde la moins impactante ; rappel fort à propos (bis)

La fréquence de l'exécution des DBCC CHECK dépend des sauvegardes. Je considère que cette fréquence doit être la même que la sauvegarde FULL, car les possibilité de récupération des données abimées se reposent sur les sauvegardes.

Là je ne suis plus complètement d'accord.
Je plussoie que "l'exécution des DBCC CHECK dépend des sauvegardes"
Plus exactement de l'instruction BACKUP DATABASE.
Mais je ne vois pas en quoi le backup différentiel est moins exposé aux erreurs qui peuvent être relevées par DBCC CHECKDB.
Pour moi c'est la même opération de copie, simplement restreinte à un volume réduit.

Faut-il faire le CHECK avant ou après le backup ?
Si on restaure systématiquement, est-ce que le CHECK est necessaire ?
Pourquoi M$ n'inclue pas le CHECK, éventuellement en option, lors du backup ? il y a bien lecture de toutes les pages ! idem pour la restauration.

Elle [la fréquence] dépend aussi du contenu des groupe de fichiers. Pour les groupe de fichiers "froid" (archive pas exemple) les placer en READONLY et faire une sauvegarde. Dès lors un seul DBCC CHECKFILEGROUP suffit, juste après la sauvegarde..

Le temps transactionnel peut être très court !
Il faut valider qu'entre la dernière sauvegarde et le passage en mode READONLY, il n'y a pas eu effectivement d'écriture (sinon, on recommence)
Il est vrai que pour les données non chaude la probabilité que ça arrive est faible (à l'inverse, à vous de revoir votre définition de données "froides")
Mais, encore une fois, faible n'est pas nulle.

Lisez attentivement le document que j'ai écrit concernant la récupération des données abimées
https://blog.developpez.com/sqlpro/p...ver-corrompues
Un des moyens de récupération les plus intéressant est la restauration de page qui peut se faire à chaud en version Enterprise.

Note : Je pense sincérement que tes contributions affichent le bon coté de toi même, et me vaut le respect professionnel que j'ai pour toi.

Cette remarque sous-entend que le CHECKDB n'est utile "que" pour la détection des pages corrompues.
Du coup, en optant pour de l'Always On correctement installé (indépendance du stockage, les caches en écriture, ...), on pourrait envisager de "jouer la stat".
Où serait le problème ?

A vous lire

[SQLpro]

Oulala tu m'obliges à une réponse avant mes vacances en plongée.... je répond rapidement...

[QUOTE=Michel.Priori;12038862]Note : Pour moi la définition d'une VLDB dépend de la capacité de traitement du serveur associé ;

Non...

dès que les opérations de maintenance posent un problème de planification, je considère qu'on n'est plus dans une base de données standard, que l'on est, peu ou prou, en face d'une VLDB.

Oui à moitié ! ...

Là je ne suis plus complètement d'accord.
Je plussoie que "l'exécution des DBCC CHECK dépend des sauvegardes"
Plus exactement de l'instruction BACKUP DATABASE.
Mais je ne vois pas en quoi le backup différentiel est moins exposé aux erreurs qui peuvent être relevées par DBCC CHECKDB.

Si tu créé des groupe de fichier READONLY alors pas besoin de les sauvegarder plus d'une fois......

Faut-il faire le CHECK avant ou après le backup ?

Bonne question, il n'y a pas de réponse toute faite, mais je préfère avant...

Si on restaure systématiquement, est-ce que le CHECK est necessaire ?

je comprend pas la question...

Pourquoi M$ n'inclue pas le CHECK, éventuellement en option, lors du backup ? il y a bien lecture de toutes les pages ! idem pour la restauration.

Le CHECK est possible lors de la sauvegarde. Mais il ne lit pas toutes les pages sur le disque... Vu qu'il capture en premier les pages en mémoire. DOnc des pages en mémoire depuis plusieurs jours peuvent être corrompues sur le disque...

Le temps transactionnel peut être très court !
Il faut valider qu'entre la dernière sauvegarde et le passage en mode READONLY, il n'y a pas eu effectivement d'écriture (sinon, on recommence)

Non... Tu peut sauvegarder le groupe de fichier en READONLY.... Le READONLY ne concerne que les tables utilisateur, pas les tables système ni les pages techniques...

Cette remarque sous-entend que le CHECKDB n'est utile "que" pour la détection des pages corrompues.
Du coup, en optant pour de l'Always On correctement installé (indépendance du stockage, les caches en écriture, ...), on pourrait envisager de "jouer la stat".
[QUOTE=Michel.Priori;12038862]AlwaysOn répare automatiquement les pages endommagées, mais il faut quelles soient lues physiquement. Cela ne remplace pas le DBCC CHECK...

A +

[Michel.Priori]

Re,

Mon objectif est de réduire au minimum les opérations en ligne.

DBCC CHECKDB WITH PHYSICAL_ONLY vs BACKUP DATABASE WITH CHECKSUM :

Le CHECK est possible lors de la sauvegarde.

La bol à propos du check pendant la sauvegarde :
https://learn.microsoft.com/en-us/sq...sum--checksum-

CHECKSUM
Specifies that the backup operation verifies each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.

https://learn.microsoft.com/en-us/sq...6#BckChecksums

if page-checksum or torn-page information is present on a given page, when backing up the page, BACKUP also verifies the checksum and torn-page status and the page ID, of the page.

En rapprochant de : https://learn.microsoft.com/fr-fr/sq...#physical_only

Limite la nature de la vérification à l'intégrité de la structure physique sur la page et les en-têtes d'enregistrement, et à l'intégrité de la cohérence d'allocation de la base de données. Cette vérification, qui vise à contrôler la cohérence physique de la base de données, inclut par ailleurs la détection des pages endommagées, des échecs de somme de contrôle et des erreurs matérielles courantes, susceptibles de compromettre les données utilisateur.

J'ai la vague impression que, sous réserve que le checksum des pages de la base soit activé, DBCC CHECKDB est redondant avec BACKUP DATABASE WITH CHECKSUM.
Surtout si on ajoute la remarque de la bol :

Si des erreurs sont signalées par DBCC CHECKDB, nous vous recommandons de restaurer la base de données à partir de sa sauvegarde au lieu d’exécuter REPAIR avec une des options correspondantes.

Mais il ne lit pas toutes les pages sur le disque... Vu qu'il capture en premier les pages en mémoire. DOnc des pages en mémoire depuis plusieurs jours peuvent être corrompues sur le disque...

Mon interprétation du point est que le DBCC force la lecture de tous les blocs des fichiers de la base, lors de l'execution.
J'ai tendance à me dire que si une page est défaillante sur disque alors que sa copie en mémoire est intègre, lors du database writer les choses vont rentrer dans l'ordre d'elle même.
Je n'arrive pas à voir où est le danger.

AlwaysOn répare automatiquement les pages endommagées, mais il faut quelles soient lues physiquement. Cela ne remplace pas le DBCC CHECK.

idem

DBCC CHECKDB complet
En plus de la validation "physique", il est opéré une validation "logique".

Comment une erreur "logique" peut arriver ?
Un bug ? j'amais entendu parlé !

Admettons, qu'on soit en face d'une erreur logique.
Quelle est la plus value d'avoir fait un DBCC regulièrement ? d'être informé plus tôt ?
Si on ne fait pas de test en conjonction avec un backup, on a peut être l'erreur dans beaucoup de fichiers de backup. Comment les repérer dans les sauvegardes ?
Si, en plus, les options de réparations sont innopérantes, que faire ? tester autant de restaurations que necessaire pour en trouver un backup database fiable (full et/ou differential) et de jouer tous les journaux qu'on aurait encore sous la main.
Si c'est bien ça (!) cela met un sacré coup aux "bonnes pratiques" https://learn.microsoft.com/fr-fr/sq...best-practices

[SQLpro]

DBCC CHECK... vérifie les pages en les lisant du disque... Pas de la mémoire en cache...
BACKUP DATABASE ... WITH CHECKSUM vérifie les pages en mémoire et pour celle qui n'y sont pas, sur le disque.

Ce n'est donc pas les mêmes opérations.

Tu peux te passer du BACKUP ... CHECKSUM, mais pas du DBCC CHECK.

A +

[Michel.Priori]

re,

Prenons un cas de figure : On a une base qui présente une erreur de cohérence.

Les actions à faire sont :

1. dbcc checkdb REPAIR_FAST
2. si pas suffisant => dbcc checkdb REPAIR_REBUILD
3. si pas suffisant => restore database
4. si problème avec la restauration => dbcc checkdb REPAIR_ALLOW_DATA_LOSS

Selon la BOL
https://learn.microsoft.com/en-us/sq...#physical_only

Therefore, using the PHYSICAL_ONLY option may cause a much shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems.

https://learn.microsoft.com/en-us/sq...repair_rebuild

Microsoft always recommends a user restore from the last known good backup as the primary method to recover from errors reported by DBCC CHECKDB.
The REPAIR_ALLOW_DATA_LOSS option isn't an alternative for restoring from a known good backup.
It is an emergency last resort option recommended for use only if restoring from a backup isn't possible.

J'en déduis que l'option DBCC CHECKDB PHYSICAL_ONLY devrait être suffisante pour pouvoir qualifier le dernier backup de "last known good backup".
Autre option pour qualifier la sauvegarde : restaurer systématiquement le dernier backup (refresh de la préprod quotidienne ?).

Ma réflexion à propos du contrôle de l’intégrité de ce qui est sauvegardé lors de l’opération de backup vise à valider si, oui ou non, le contrôle fait à ce moment est suffisant pour pouvoir qualifier la sauvegarde de "last known good backup". Bien entendu en dehors des erreurs de stockage du fichier de backup lui-même.

Dans le cas contraire je m’inscris en faux par rapport à la remarque :

Je considère que cette fréquence doit être la même que la sauvegarde FULL, car les possibilité de récupération des données abimées se reposent sur les sauvegardes

Seul le backup LOG peut être exempté du contrôle physique ; les backup database complet ou différentiels copient les pages, donc sont succeptibles de contenir des pages défaillantes.
Du coup en ne "protégeant" pas les backup différentiel on prend le risque de devoir rejouer tous les journaux ; ce qui peut être très long !

Et pour finir, si l'erreur est "logique", que ni le backup ni le restore sont en mesure de lancer une erreur, et que les opérations de réparation de DBCC sont inopérantes alors, contrairement à ce dit la documentation il faut faire un DBCC CHECKDB complet à chaque sauvegarde (et en espérant que les backup log n'ont pas capturé l'erreur, sinon elle sera reproduite !).
Et encore ! une fois que l'instruction DBCC CHECKDB a soulevé l'erreur (certes, prématurément par rapport à un usage normal) on reste comme gros-jean devant.

Bref il me semble que la documentation n'est pas suffisante sur ces points.
Et comme je n'ai rencontré qu'une fois le problème (suite à des "live motion" non controlés) j'ai du mal à avoir un retour d'expérience.

[SQLpro]

re,

Prenons un cas de figure : On a une base qui présente une erreur de cohérence.

Les actions à faire sont :

1. dbcc checkdb REPAIR_FAST
2. si pas suffisant => dbcc checkdb REPAIR_REBUILD
3. si pas suffisant => restore database
4. si problème avec la restauration => dbcc checkdb REPAIR_ALLOW_DATA_LOSS

NON !!! commencer par un RESTORE PAGE....
REPAIR FAST ne fais rien du tout... Maintains syntax for backward compatibility only. No repair actions are performed.
REPAIR_REBUILD ne répare pas tout.... This argument doesn't repair errors involving FILESTREAM data.

J'en déduis que l'option DBCC CHECKDB PHYSICAL_ONLY devrait être suffisante pour pouvoir qualifier le dernier backup de "last known good backup".
Autre option pour qualifier la sauvegarde : restaurer systématiquement le dernier backup (refresh de la préprod quotidienne ?).

Oui,
Non, car tu peut avoir une erreur lors de la restauration... Sera-ce une erreur de la sauvegarde ou de l'opération de restauration....
Seul RESTORE VERIFYONLY te garantie que la sauvegarde peut être appliquée

Ma réflexion à propos du contrôle de l’intégrité de ce qui est sauvegardé lors de l’opération de backup vise à valider si, oui ou non, le contrôle fait à ce moment est suffisant pour pouvoir qualifier la sauvegarde de "last known good backup". Bien entendu en dehors des erreurs de stockage du fichier de backup lui-même.

Non

A +

[Michel.Priori]

Salut Frédéric,

je suis désolé de m'acharner sur toi mais tu es le seul à repondre à ce sujet ; merci de ton implication dans ce fil de conversation.

NON !!! commencer par un RESTORE PAGE....

Ok,
Il faudrait juste modifier ta page https://blog.developpez.com/sqlpro/p...ver-corrompues

REPAIR FAST ne fais rien du tout... Maintains syntax for backward compatibility only. No repair actions are performed.
REPAIR_REBUILD ne répare pas tout.... This argument doesn't repair errors involving FILESTREAM data.

Là aussi ta page n'évoque pas ce manque.
Faut avouer que le recours à du filestream n'est pas une option systèmatiquement activée.
Ceci dit je n'ai jamais eu à restaurer tout ou partie d'un filestream independament de la base. Faudrait que j'y consacre un peu de temps...

Oui,

j'imagine que c'est à propos de

l'option DBCC CHECKDB PHYSICAL_ONLY devrait être suffisante pour pouvoir qualifier le dernier backup de "last known good backup".

Non, car tu peut avoir une erreur lors de la restauration... Sera-ce une erreur de la sauvegarde ou de l'opération de restauration....
Seul RESTORE VERIFYONLY te garantie que la sauvegarde peut être appliquée

C'est drôle de considérer que RESTORE VERIFYONLY puisse te garantir qu'on puisse faire une restauration, alors que faire la restauration en vrai n'en ne permet pas de le garantir.
Du coup les opérations DBCC CHECKDB PHYSICAL_ONLY et RESTORE VERIFYONLY mènent toutes les 2 à qualifier le backup.

Reprenons : On a une base qui présente une erreur de cohérence.

Les actions à faire sont :
restauration de page
si pas suffisant => dbcc checkdb REPAIR_REBUILD
si pas suffisant => dbcc checkdb REPAIR_ALLOW_DATA_LOSS
si pas suffisant => restauration de la base de donnée

La chaine récurente de backup étant :
* backup database ... filegroup= ... Choix des FG à adapter en fonction des bascules READ_WRITE et du statut READ_ONLY ; peu importe que le backup soit différentiel ou complet
* DBCC CHECKFILEGROUP ... WITH PHYSICAL_ONLY de ce qui a été sauvegardé.
* Si erreur => déclencher un ticket et appliquer les points listés ci dessus
* Sinon => RESTORE VERIFYONLY
** si erreur => ticket
** sinon => fin du backup

Pour les bases non VLDB = la même, pour toute la base (on évite la conversaiton des groupes de fichiers)

[SQLpro]

... C'est drôle de considérer que RESTORE VERIFYONLY puisse te garantir qu'on puisse faire une restauration, alors que faire la restauration en vrai n'en ne permet pas de le garantir.
Du coup les opérations DBCC CHECKDB PHYSICAL_ONLY et RESTORE VERIFYONLY mènent toutes les 2 à qualifier le backup.

Non, pas du tout... DBCC CHECKDB PHYSICAL_ONLY concerne la base, RESTORE VERIFYONLY concerne le fichier de restauration... Le premier te garantie que la base est physiquement intègre, le second te garantie que le fichier de sauvegarde permet de restaurer la base, mais avec d'éventuelles corruptions... C'est pourquoi il y a la fameuse options de restauration CONTNUE_AFTER_ERROR...

Reprenons : On a une base qui présente une erreur de cohérence.

Les actions à faire sont :
restauration de page
si pas suffisant => dbcc checkdb REPAIR_REBUILD
si pas suffisant => dbcc checkdb REPAIR_ALLOW_DATA_LOSS
si pas suffisant => restauration de la base de donnée

Oui mais, je rajouterai à ta dernière commande l'option CONTNUE_AFTER_ERROR...

A +

[Michel.Priori]

Merci pour les précisions

je laisse quand même le fil ouvert en espérant que quelqu'un puisse expliciter/démontrer quels sont les contrôles de page qui sont, par défaut, faits avec BACKUP ou RESTORE comparé aux contrôles de page qui sont faits avec DBCC CHECK[*] WITH PHYSICAL_ONLY.