{"id":3287,"date":"2012-04-10T13:40:56","date_gmt":"2012-04-10T12:40:56","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=3287"},"modified":"2019-09-13T14:20:54","modified_gmt":"2019-09-13T13:20:54","slug":"production-sql-server-controle-de-coherence","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/","title":{"rendered":"Production SQL Server : Contr\u00f4le de coh\u00e9rence"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Sixi\u00e8me article de la s\u00e9rie Une approche pragmatique de la production SQL Server<\/a>, c\u2019est le contr\u00f4le de coh\u00e9rence des objets d\u2019une base qui est abord\u00e9 ici.<\/p>\n

Coh\u00e9rence d\u2019un objet ?<\/strong>
\nUn fichier de donn\u00e9es est constitu\u00e9 de pages (ensembles contigu de 8 k. octets). Lorsque SQL Server \u00e9crit une page sur disque, il calcule une signature (CHECKSUM) sur quelques octets et l\u2019\u00e9crit dans l\u2019ent\u00eate de la page. Cette signature est recalcul\u00e9e lors d\u2019une lecture et compar\u00e9e \u00e0 la valeur stock\u00e9e dans l\u2019ent\u00eate pour garantir la coh\u00e9rence.<\/p>\n

Cet ent\u00eate contient aussi plusieurs informations comme l\u2019objet auquel elle appartient, la page qui la pr\u00e9c\u00e8de dans le cha\u00eenage de l\u2019objet et la page qui la suit. Certaines pages sp\u00e9cifiques (pages IAM) listent l\u2019ensemble des pages d\u2019un objet.<\/p>\n

Probl\u00e8me de coh\u00e9rence ?<\/strong>
\nLors de la lecture d\u2019une page depuis le disque, la signature est calcul\u00e9e et compar\u00e9e \u00e0 la signature stock\u00e9e. Si les deux valeurs sont diff\u00e9rentes, la page est consid\u00e9r\u00e9e non coh\u00e9rente.
\nSi la page IAM d\u2019un objet r\u00e9f\u00e9rence une page, et que cette page r\u00e9f\u00e9rence un autre objet, il y a incoh\u00e9rence.
\nSi une page A d\u00e9signe une page B comme \u00ab suivante \u00bb dans le cha\u00eenage et que cette page B ne d\u00e9signe pas la page A comme \u00ab pr\u00e9c\u00e9dente \u00bb, il y a incoh\u00e9rence.
\nSQL Server refusera de lire une page incoh\u00e9rente et renverra une erreur (que cette lecture corresponde \u00e0 un \u00ab select \u00bb ou un \u00ab backup database \u00bb par exemple\u2026).<\/p>\n

Et concr\u00eatement ?<\/strong>
\nLorsqu\u2019une page est incoh\u00e9rente, certains objets ne seront plus consultables, et les t\u00e2ches de maintenance critiques comme la sauvegarde ne fonctionneront plus. Il est donc important de d\u00e9tecter au plus vite un probl\u00e8me de coh\u00e9rence.<\/p>\n

Il est possible de r\u00e9cup\u00e9rer une page corrompue sans perdre de donn\u00e9es sous certaines conditions :
\n\u2022\tSoit la page corrompue appartient \u00e0 un indexe non clust\u00e9ris\u00e9. La page ne contient donc aucune donn\u00e9e, l\u2019indexe peut \u00eatre reconstruit sans risque (option REPAIR_REBUILD de la commande DBCC CHECKDB)
\n\u2022\tSoit la base est en miroir. Le m\u00e9canisme Automatic Page Repair<\/a> (\u00e0 partir de SQL Server 2008 ) utilisera la base miroir pour r\u00e9cup\u00e9rer la page corrompue.
\n\u2022\tSoit la base est en mode de recouvrement COMPLET. Il est possible de restaurer une ou plusieurs pages (\u00e0 partir de la derni\u00e8re sauvegarde de base) puis de r\u00e9appliquer les modifications sur ces pages (restauration des sauvegardes transactionnelles). Cette op\u00e9ration entraine une indisponibilit\u00e9, mais elle est minime.<\/p>\n

Donc si vos bases sont en miroir ou qu\u2019elles sont en mode de recouvrement COMPLET, la fr\u00e9quence de lancement du contr\u00f4le de coh\u00e9rence peut \u00eatre plus faible. Pour les autres bases, la fr\u00e9quence de lancement d\u00e9pend de la perte de donn\u00e9es acceptable.<\/p>\n

En pratique<\/strong>
\nLe lancement du contr\u00f4le est simple, puisqu\u2019il consiste simplement \u00e0 lancer la commande DBCC CHECKDB.
\nLe script suivant permet de lancer DBCC CHECKDB sur toutes les bases, \u00e0 l\u2019exception des Snapshots, de tempdb et des bases en lecture seule, et ne remonte que les messages d\u2019erreur :
\nEXEC sp_msforeachdb 'IF (CAST(DATABASEPROPERTYEX(''?'',''Updateability'') as varchar(15)) = ''READ_WRITE'' AND ''?'' NOT IN (''tempdb''))
\nDBCC CHECKDB(''?'') WITH NO_INFOMSGS, ALL_ERRORMSGS
\n'<\/code><\/p>\n

Un fichier de dump est cr\u00e9\u00e9 chaque fois que DBCC CHECKDB rencontre une erreur de coh\u00e9rence. La remont\u00e9 d\u2019alertes lors de corruption peut donc correspondre \u00e0 l\u2019arriv\u00e9e d\u2019un nouveau fichier SQLDUMP<Id>.txt ( o\u00f9 <Id> est un nombre \u00e0 4 chiffres qui s\u2019incr\u00e9mente automatiquement).<\/p>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"

Sixi\u00e8me article de la s\u00e9rie Une approche pragmatique de la production SQL Server, c\u2019est le contr\u00f4le de coh\u00e9rence des objets d\u2019une base qui est abord\u00e9 ici. Coh\u00e9rence d\u2019un objet ? Un fichier de donn\u00e9es est constitu\u00e9 de pages (ensembles contigu… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":14,"featured_media":7885,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[206,212],"class_list":["post-3287","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlserver","tag-howto","tag-production"],"yoast_head":"\nProduction SQL Server : Contr\u00f4le de coh\u00e9rence - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Production SQL Server : Contr\u00f4le de coh\u00e9rence - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"Sixi\u00e8me article de la s\u00e9rie Une approche pragmatique de la production SQL Server, c\u2019est le contr\u00f4le de coh\u00e9rence des objets d\u2019une base qui est abord\u00e9 ici. Coh\u00e9rence d\u2019un objet ? Un fichier de donn\u00e9es est constitu\u00e9 de pages (ensembles contigu… Continuer la lecture →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2012-04-10T12:40:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-13T13:20:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2012\/04\/consistency.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"450\" \/>\n\t<meta property=\"og:image:height\" content=\"298\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Benjamin VESAN\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Benjamin VESAN\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/\"},\"author\":{\"name\":\"Benjamin VESAN\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e\"},\"headline\":\"Production SQL Server : Contr\u00f4le de coh\u00e9rence\",\"datePublished\":\"2012-04-10T12:40:56+00:00\",\"dateModified\":\"2019-09-13T13:20:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/\"},\"wordCount\":610,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"howto\",\"Production\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/\",\"name\":\"Production SQL Server : Contr\u00f4le de coh\u00e9rence - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2012-04-10T12:40:56+00:00\",\"dateModified\":\"2019-09-13T13:20:54+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Production SQL Server : Contr\u00f4le de coh\u00e9rence\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e\",\"name\":\"Benjamin VESAN\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/bvesan\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Production SQL Server : Contr\u00f4le de coh\u00e9rence - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/","og_locale":"fr_FR","og_type":"article","og_title":"Production SQL Server : Contr\u00f4le de coh\u00e9rence - Capdata TECH BLOG","og_description":"Sixi\u00e8me article de la s\u00e9rie Une approche pragmatique de la production SQL Server, c\u2019est le contr\u00f4le de coh\u00e9rence des objets d\u2019une base qui est abord\u00e9 ici. Coh\u00e9rence d\u2019un objet ? Un fichier de donn\u00e9es est constitu\u00e9 de pages (ensembles contigu… Continuer la lecture →","og_url":"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2012-04-10T12:40:56+00:00","article_modified_time":"2019-09-13T13:20:54+00:00","og_image":[{"width":450,"height":298,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2012\/04\/consistency.jpg","type":"image\/jpeg"}],"author":"Benjamin VESAN","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Benjamin VESAN","Dur\u00e9e de lecture estim\u00e9e":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/"},"author":{"name":"Benjamin VESAN","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e"},"headline":"Production SQL Server : Contr\u00f4le de coh\u00e9rence","datePublished":"2012-04-10T12:40:56+00:00","dateModified":"2019-09-13T13:20:54+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/"},"wordCount":610,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["howto","Production"],"articleSection":["SQL Server"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/","url":"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/","name":"Production SQL Server : Contr\u00f4le de coh\u00e9rence - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2012-04-10T12:40:56+00:00","dateModified":"2019-09-13T13:20:54+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/production-sql-server-controle-de-coherence\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Production SQL Server : Contr\u00f4le de coh\u00e9rence"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e","name":"Benjamin VESAN","url":"https:\/\/blog.capdata.fr\/index.php\/author\/bvesan\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/3287","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=3287"}],"version-history":[{"count":17,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/3287\/revisions"}],"predecessor-version":[{"id":3498,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/3287\/revisions\/3498"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/7885"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=3287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=3287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=3287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}