{"id":1066,"date":"2008-03-21T23:23:07","date_gmt":"2008-03-21T23:23:07","guid":{"rendered":"\/blogs\/paul\/post\/Inside-The-Storage-Engine-Does-turning-on-page-checksums-discard-any-torn-page-protection.aspx"},"modified":"2008-03-21T23:23:07","modified_gmt":"2008-03-21T23:23:07","slug":"inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/","title":{"rendered":"Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?"},"content":{"rendered":"


\n

This is a really interesting question that came up in the Microsoft Certified Architect class I’m teaching at present – if a database has torn-page protection enabled, and page checksums are enabled, is all the existing torn-page detection lost?<\/P>
\n

This is an important question, because enabling page checksums doesn’t suddenly make all allocated pages be protected by page checksums (it’s not until a page is read into the buffer pool, modified, and then written back to disk, that it gets a page checksum). If all the existing torn-page protection is discarded when page checksums are enabled, then the pages would be unprotected until they got page checksums on. I couldn’t remember the answer, so I experimented!<\/P>
\n

My idea was to create a database with torn-page protection, create a table with a simulated torn-page in it, then enable page checksums and see if the torn-page was still reported.<\/P>
\n


\n

— Create the test database
<\/FONT>USE<\/FONT><\/FONT> <\/FONT>master<\/FONT>;
<\/FONT><\/FONT>GO
<\/FONT>CREATE<\/FONT><\/FONT> <\/FONT>DATABASE<\/FONT> ChecksumTest<\/FONT>;
<\/FONT><\/FONT>GO
<\/FONT>USE<\/FONT><\/FONT> ChecksumTest<\/FONT>;
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

— Explicitly set the database to have torn-page detection
<\/FONT>ALTER<\/FONT><\/FONT> <\/FONT>DATABASE<\/FONT> ChecksumTest <\/FONT>SET<\/FONT> <\/FONT>PAGE_VERIFY<\/FONT> <\/FONT>TORN_PAGE_DETECTION<\/FONT>;
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

— Create a test table and insert a row.
<\/FONT>CREATE<\/FONT><\/FONT> <\/FONT>TABLE<\/FONT> BrokenTable <\/FONT>(<\/FONT><\/FONT>c1 INT<\/FONT>,<\/FONT> c2 CHAR<\/FONT> (<\/FONT>1000));
<\/FONT><\/FONT>INSERT<\/FONT><\/FONT> <\/FONT>INTO<\/FONT> BrokenTable<\/FONT><\/FONT> VALUES<\/FONT> (<\/FONT>1,<\/FONT> ‘a’<\/FONT>);
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

— Ensure the page is written to disk and then tossed from the buffer pool
<\/FONT>CHECKPOINT<\/FONT><\/FONT>;
<\/FONT><\/FONT>GO
<\/FONT>DBCC<\/FONT><\/FONT> DROPCLEANBUFFERS<\/FONT>;
<\/FONT><\/FONT>GO<\/FONT><\/P><\/BLOCKQUOTE>
\n

Now I’m going to examine the page. There are two bits in the page header that specify whether the page is protected by torn-page detection or with a page checksum. Specifically, the m_flagBits<\/STRONG><\/EM> field will have 0x100 set if the page is encoded for torn-page protection, and 0x200 set if the page has a page-checksum stored on it, and the page has not been modified (i.e. the checksum is stillvalid). You should not see the 0x100 bit set as torn-page encoding is removed when the page is read into the buffer pool – UNLESS the page IS actually torn, in which case the encoding is NOT removed.<\/FONT><\/FONT><\/P>
\n


\n

<\/FONT>sp_allocationmetadata ‘BrokenTable’<\/FONT>;
<\/FONT><\/FONT><\/FONT>GO
<\/FONT>DBCC<\/FONT><\/FONT> TRACEON <\/FONT>(<\/FONT>3604<\/FONT>);
<\/FONT><\/FONT>GO
<\/FONT>DBCC<\/FONT><\/FONT> PAGE <\/FONT>(<\/FONT>‘ChecksumTest’<\/FONT>,<\/FONT> 1<\/FONT>,<\/FONT> 143<\/FONT>,<\/FONT> 3<\/FONT>);
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

<snip><\/FONT><\/P>
\n

m_pageId = (1:143)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000<\/STRONG>
m_objId (AllocUnitId.idObj) = 67     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594042318848                                
Metadata: PartitionId = 72057594038321152                                 Metadata: IndexId = 0
Metadata: ObjectId = 2073058421      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 1008                       m_slotCnt = 2                        m_freeCnt = 6070
m_freeData = 2118                    m_reservedCnt = 0                    m_lsn = (28:183:2)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 770<\/STRONG><\/FONT>      <\/P>
\n

<snip><\/FONT>     <\/P><\/BLOCKQUOTE>
\n

In this case the torn-page encoding has been removed, and the page is fine. Once I’ve corrupted the page on disk, it’s tricky to be able to see it with DBCC PAGE. I managed to catch it once and saw the following:<\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/P>
\n


\n

m_pageId = (1:143)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8100<\/STRONG>
m_objId (AllocUnitId.idObj) = 67     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594042318848                                
Metadata: PartitionId = 72057594038321152                                 Metadata: IndexId = 0
Metadata: ObjectId = 2073058421      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 1008                       m_slotCnt = 1                        m_freeCnt = 7083
m_freeData = 1107                    m_reservedCnt = 0                    m_lsn = (28:81:20)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 41949233<\/STRONG> <\/P><\/FONT><\/BLOCKQUOTE>
\n

Now if I try to select from the table I get:          <\/FONT><\/P>
\n


\n

SELECT<\/FONT><\/FONT> <\/FONT>*<\/FONT> <\/FONT>FROM<\/FONT> BrokenTable<\/FONT>;
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

Msg 824, Level 24, State 2, Line 1<\/FONT><\/P>
\n

SQL Server detected a logical consistency-based I\/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xaaaaa82a). It occurred during a read of page (1:143) in database ID 8 at offset 0x0000000011e000 in file ‘C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\ChecksumTest.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.<\/FONT><\/P><\/BLOCKQUOTE>
\n

The crux of the question is whether this will still be reported if the database switches to page checksums – let’s try:<\/FONT><\/P>
\n


\n

ALTER<\/FONT><\/FONT> DATABASE<\/FONT> checksumtest SET<\/FONT> PAGE_VERIFY<\/FONT> CHECKSUM<\/FONT>;
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

SELECT<\/FONT><\/FONT> <\/FONT>*<\/FONT> <\/FONT>FROM<\/FONT> BrokenTable<\/FONT><\/FONT>;
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

Msg 824, Level 24, State 2, Line 1<\/FONT><\/P>
\n

SQL Server detected a logical consistency-based I\/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xaaaaa82a). It occurred during a read of page (1:143) in database ID 8 at offset 0x0000000011e000 in file ‘C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\ChecksumTest.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.<\/FONT><\/P><\/BLOCKQUOTE>
\n

Cool! The answer is YES – the torn-page is still detected, because the bit in the page header specifies which page protection algorithm the page is using. In fact, it even works if you turn off page checksums and torn-page detection completely.<\/FONT><\/P><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"

This is a really interesting question that came up in the Microsoft Certified Architect class I’m teaching at present – if a database has torn-page protection enabled, and page checksums are enabled, is all the existing torn-page detection lost? This is an important question, because enabling page checksums doesn’t suddenly make all allocated pages be […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30,48,53,62,63],"tags":[],"class_list":["post-1066","post","type-post","status-publish","format-standard","hentry","category-corruption","category-inside-the-storage-engine","category-io-subsystems","category-on-disk-structures","category-page-checksums"],"yoast_head":"\nInside The Storage Engine: Does turning on page checksums discard any torn-page protection? - 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\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Inside The Storage Engine: Does turning on page checksums discard any torn-page protection? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This is a really interesting question that came up in the Microsoft Certified Architect class I’m teaching at present – if a database has torn-page protection enabled, and page checksums are enabled, is all the existing torn-page detection lost? This is an important question, because enabling page checksums doesn’t suddenly make all allocated pages be […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-03-21T23:23:07+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=\"7 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\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/\",\"name\":\"Inside The Storage Engine: Does turning on page checksums discard any torn-page protection? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-03-21T23:23:07+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?\"}]},{\"@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":"Inside The Storage Engine: Does turning on page checksums discard any torn-page protection? - 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\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/","og_locale":"en_US","og_type":"article","og_title":"Inside The Storage Engine: Does turning on page checksums discard any torn-page protection? - Paul S. Randal","og_description":"This is a really interesting question that came up in the Microsoft Certified Architect class I’m teaching at present – if a database has torn-page protection enabled, and page checksums are enabled, is all the existing torn-page detection lost? This is an important question, because enabling page checksums doesn’t suddenly make all allocated pages be […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/","og_site_name":"Paul S. Randal","article_published_time":"2008-03-21T23:23:07+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/","name":"Inside The Storage Engine: Does turning on page checksums discard any torn-page protection? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-03-21T23:23:07+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-does-turning-on-page-checksums-discard-any-torn-page-protection\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?"}]},{"@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\/1066","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=1066"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1066\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1066"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1066"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1066"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}