{"id":1018,"date":"2008-08-27T17:11:27","date_gmt":"2008-08-27T17:11:27","guid":{"rendered":"\/blogs\/paul\/post\/Search-Engine-QA-26-Myths-around-causing-corruption.aspx"},"modified":"2008-08-27T17:11:27","modified_gmt":"2008-08-27T17:11:27","slug":"search-engine-qa-26-myths-around-causing-corruption","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/","title":{"rendered":"Search Engine Q&#038;A #26: Myths around causing corruption"},"content":{"rendered":"<p><FONT face=Verdana size=2><br \/>\n<P>Every so often I&#8217;ll see posts on the various data corruption forums discussing causes of corruption. In this post I want to debunk some of the myths around what can cause corruption. There are really two types of corruption to deal with, physical corruption and logical corruption.<\/P><br \/>\n<P><STRONG>Physical corruption<\/STRONG><\/P><br \/>\n<P>This is where something has altered the contents of a data or log file sector with no regard for what is being stored there. Possible causes of physical corruption are:<\/P><br \/>\n<UL><br \/>\n<LI>Problem with the I\/O subsystem&nbsp;(99.8% of all cases I&#8217;ve ever seen &#8211; only 3 nines as I&#8217;d estimate I&#8217;ve seen around about a thousand corruption cases). Remember the I\/O subsystem is everything underneath SQL Server in the I\/O stack &#8211; including the OS, 3rd-party file system filter drivers, device drivers, RAID controllers, SAN controllers, network hardware, drives themselves, and so on. Millions of lines of code and lots of moving parts spinning very fast, very close to very fragile pieces of metal oxide (I once heard Jim Gray liken a disk drive head to a 747 jumbo jet flying at 500 mph at a height of 1\/4 inch from the ground&#8230;)<br \/>\n<LI>Problem with the host machine hardware (0.1% of cases). Most of the time this is a memory error.<br \/>\n<LI>SQL Server bugs (0.1% of cases). Yes, there have been corruption bugs. Every piece of software has bugs. There are KB articles describing bugs.<br \/>\n<LI>Deliberate introduction of corruption using a hex editor or other means.<\/LI><\/UL><br \/>\n<P>Physical corruption is what <FONT face=\"Courier New\">DBCC CHECKDB<\/FONT> usually reports and the majority of cases are caused by a physical failures of some kind, with the minority caused by humans &#8211; software bugs.<\/P><br \/>\n<P><STRONG>Logical corruption<\/STRONG><\/P><br \/>\n<P>This is where something has altered some data so that a data relationship is broken. Possible causes of logical corruption are:<\/P><br \/>\n<UL><br \/>\n<LI>Humans<\/LI><\/UL><br \/>\n<P>:-) Okay&#8230;<\/P><br \/>\n<UL><br \/>\n<LI>Application bug. The application deletes one part of an inherent data relationship but not the other. Or the application designer doesn&#8217;t implement a constraint properly. Or the application designer doesn&#8217;t cope with a transaction roll-back properly. You get the idea.<br \/>\n<LI>Accidental update\/delete. Someone deletes or updates some data incorrectly.<br \/>\n<LI>SQL Server bug. See above.<br \/>\n<LI><FONT face=\"Courier New\">DBCC CHECKDB<\/FONT> when using the <FONT face=\"Courier New\">REPAIR_ALLOW_DATA_LOSS<\/FONT> option. As is documented in Books Online, and I&#8217;ve blogged about and mentioned when lecturing, if you run repair, it doesn&#8217;t take into account any inherent or explicit constraints on the data.<\/LI><\/UL><br \/>\n<P>The point here is that a physical failure of a component does not cause logical corruption, it causes physical corruption. Conversely, application errors cause logical corruption, not physical corruption. <FONT face=\"Courier New\">DBCC CHECKDB<\/FONT> errors are about physical corruption (okay, with the inclusion of <FONT face=\"Courier New\">DBCC CHECKCATALOG<\/FONT> code in 2005, it will find cases where the DBA has manually altered the system tables, causing logical corruption) and applications cannot cause physical corruption as they can only manipulate data through SQL Server. If an application hits a SQL Server bug which causes physical corruption, that&#8217;s still not the application causing physical corruption, it&#8217;s SQL Server.<\/P><br \/>\n<P>So &#8211; on to the myths.<\/P><br \/>\n<UL><br \/>\n<LI>Can an application cause physical corruption? No.<br \/>\n<LI>Can stopping a shrink operation cause corruption of any kind? No.<br \/>\n<LI>Can stopping an index rebuild cause corruption of any kind? No.<br \/>\n<LI>Can running <FONT face=\"Courier New\">DBCC CHECKDB<\/FONT> without repair cause corruption of any kind? No.<br \/>\n<LI>Can creating a database snapshot cause corruption of any kind? No.<\/LI><\/UL><br \/>\n<P>Hope this helps.<\/P><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Every so often I&#8217;ll see posts on the various data corruption forums discussing causes of corruption. In this post I want to debunk some of the myths around what can cause corruption. There are really two types of corruption to deal with, physical corruption and logical corruption. Physical corruption This is where something has altered [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,22,30,34,47,53,62,74,78,81],"tags":[],"class_list":["post-1018","post","type-post","status-publish","format-standard","hentry","category-bad-advice","category-checkdb-from-every-angle","category-corruption","category-dbcc","category-indexes-from-every-angle","category-io-subsystems","category-on-disk-structures","category-repair","category-search-engine-q-and-a","category-shrink"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Search Engine Q&amp;A #26: Myths around causing corruption - Paul S. Randal<\/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:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Search Engine Q&amp;A #26: Myths around causing corruption - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Every so often I&#8217;ll see posts on the various data corruption forums discussing causes of corruption. In this post I want to debunk some of the myths around what can cause corruption. There are really two types of corruption to deal with, physical corruption and logical corruption. Physical corruption This is where something has altered [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-08-27T17:11:27+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\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\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/\",\"name\":\"Search Engine Q&A #26: Myths around causing corruption - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-08-27T17:11:27+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Search Engine Q&#038;A #26: Myths around causing corruption\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Search Engine Q&A #26: Myths around causing corruption - Paul S. Randal","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:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/","og_locale":"en_US","og_type":"article","og_title":"Search Engine Q&A #26: Myths around causing corruption - Paul S. Randal","og_description":"Every so often I&#8217;ll see posts on the various data corruption forums discussing causes of corruption. In this post I want to debunk some of the myths around what can cause corruption. There are really two types of corruption to deal with, physical corruption and logical corruption. Physical corruption This is where something has altered [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/","og_site_name":"Paul S. Randal","article_published_time":"2008-08-27T17:11:27+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/","name":"Search Engine Q&A #26: Myths around causing corruption - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-08-27T17:11:27+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Search Engine Q&#038;A #26: Myths around causing corruption"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1018","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=1018"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1018\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1018"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1018"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1018"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}