{"id":716,"date":"2010-04-15T13:05:00","date_gmt":"2010-04-15T13:05:00","guid":{"rendered":"\/blogs\/paul\/post\/A-SQL-Server-DBA-myth-a-day-(1530)-checkpoint-only-writes-pages-from-committed-transactions.aspx"},"modified":"2017-07-20T08:13:35","modified_gmt":"2017-07-20T15:13:35","slug":"a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/","title":{"rendered":"A SQL Server DBA myth a day: (15\/30) checkpoint only writes pages from committed transactions"},"content":{"rendered":"<p style=\"text-align: justify;\">(Look in the <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/category\/misconceptions\/\">Misconceptions<\/a> blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our <a href=\"https:\/\/www.sqlskills.com\/join-the-sqlskills-insider-community\/\" target=\"_blank\" rel=\"noopener noreferrer\">Insider list<\/a>, plus my online <a href=\"https:\/\/www.pluralsight.com\/courses\/sqlserver-myths\" target=\"_blank\" rel=\"noopener noreferrer\">Myths and Misconceptions training course<\/a> on Pluralsight.)<\/p>\n<p style=\"text-align: justify;\">My DBA Mythbusters session went *really* well at SQL Connections yesterday, and now I&#8217;m half way through my debunking month!<\/p>\n<p><strong>Myth #15:<\/strong> <em>checkpoint only writes pages from committed transactions<\/em>.<\/p>\n<p>FALSE<\/p>\n<p style=\"text-align: justify;\">This myth has persisted for *ages* and is linked to a misunderstanding of how the overall logging and recovery system works. A checkpoint always writes out all pages that have changed (known as being <em>marked dirty<\/em>) since the last checkpoint, or since the page was read in from disk. It doesn&#8217;t matter whether the transaction that changed a page has committed or not &#8211; the page is written to disk regardless. The only exception is for tempdb, where data pages are not written to disk as part of a checkpoint. Here are some blog post links with deeper information:<\/p>\n<ul>\n<li>TechNet Magazine article on <em><a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/2009.02.logging.aspx\">Understanding Logging and Recovery in SQL Server<\/a><\/em><\/li>\n<li>Blog post: <em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/\">How do checkpoints work and what gets logged<\/a><\/em><\/li>\n<li>Blog post: <em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/what-does-checkpoint-do-for-tempdb\/\">What does checkpoint do for tempdb?<\/a><\/em><\/li>\n<\/ul>\n<p>You can watch what a checkpoint operation is doing using a few trace flags:<\/p>\n<ul>\n<li>3502: writes to the error log when a checkpoint starts and finishes<\/li>\n<li>3504: writes to the error log information about what is written to disk<\/li>\n<\/ul>\n<p>To use these trace flags, you must enable them for all threads using <em>DBCC TRACEON (3502, 3504, -1)<\/em> otherwise you won&#8217;t see any output.<\/p>\n<p>Here&#8217;s a quick script that proves that dirty pages from uncommitted transactions are written out during a checkpoint. First the setup:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nCREATE DATABASE &#x5B;CheckpointTest];\r\nGO\r\nUSE &#x5B;CheckpointTest];\r\nGO\r\nCREATE TABLE &#x5B;t1] (&#x5B;c1] INT IDENTITY, &#x5B;c2] CHAR (8000) DEFAULT 'a');\r\nCREATE CLUSTERED INDEX &#x5B;t1c1] on &#x5B;t1] (&#x5B;c1]);\r\nGO\r\n\r\nSET NOCOUNT ON;\r\nGO\r\n\r\nCHECKPOINT;\r\nGO\r\n\r\nDBCC TRACEON (3502, 3504, -1);\r\nGO\r\n<\/pre>\n<p>And now an uncommitted transaction that causes 10MB of pages to be dirtied, followed by a checkpoint:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nBEGIN TRAN;\r\nGO\r\nINSERT INTO &#x5B;t1] DEFAULT VALUES;\r\nGO 1280\r\n\r\nCHECKPOINT;\r\nGO\r\n<\/pre>\n<p>And in the error log I see:<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n2010-04-15 13:31:25.09 spid52\u00a0\u00a0\u00a0\u00a0\u00a0 DBCC TRACEON 3502, server process ID (SPID) 52. This is an informational message only; no user action is required.\r\n2010-04-15 13:31:25.09 spid52\u00a0\u00a0\u00a0\u00a0\u00a0 DBCC TRACEON 3504, server process ID (SPID) 52. This is an informational message only; no user action is required.\r\n2010-04-15 13:31:25.09 spid52\u00a0\u00a0\u00a0\u00a0\u00a0 Ckpt dbid 8 started (0)\r\n2010-04-15 13:31:25.09 spid52\u00a0\u00a0\u00a0\u00a0\u00a0 About to log Checkpoint begin.\r\n2010-04-15 13:31:25.09 spid52\u00a0\u00a0\u00a0\u00a0\u00a0 Ckpt dbid 8 phase 1 ended (0)\r\n2010-04-15 13:31:25.71 spid52\u00a0\u00a0\u00a0\u00a0\u00a0 FlushCache: cleaned up 1297 bufs with 50 writes in 625 ms (avoided 0 new dirty bufs)\r\n2010-04-15 13:31:25.71 spid52\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 average throughput:\u00a0 16.21 MB\/sec, I\/O saturation: 70\r\n2010-04-15 13:31:25.71 spid52\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 last target outstanding: 2\r\n2010-04-15 13:31:25.71 spid52\u00a0\u00a0\u00a0\u00a0\u00a0 About to log Checkpoint end.\r\n2010-04-15 13:31:25.71 spid52\u00a0\u00a0\u00a0\u00a0\u00a0 Ckpt dbid 8 complete\r\n<\/pre>\n<p>Clearly all the pages were written out, even though the transaction had not committed.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) My DBA Mythbusters session went *really* well at SQL Connections [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,61],"tags":[],"class_list":["post-716","post","type-post","status-publish","format-standard","hentry","category-checkpoint","category-misconceptions"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>A SQL Server DBA myth a day: (15\/30) checkpoint only writes pages from committed transactions - 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\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A SQL Server DBA myth a day: (15\/30) checkpoint only writes pages from committed transactions - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) My DBA Mythbusters session went *really* well at SQL Connections [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-04-15T13:05:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-07-20T15:13:35+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=\"2 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\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/\",\"name\":\"A SQL Server DBA myth a day: (15\/30) checkpoint only writes pages from committed transactions - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-04-15T13:05:00+00:00\",\"dateModified\":\"2017-07-20T15:13:35+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A SQL Server DBA myth a day: (15\/30) checkpoint only writes pages from committed transactions\"}]},{\"@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":"A SQL Server DBA myth a day: (15\/30) checkpoint only writes pages from committed transactions - 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\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/","og_locale":"en_US","og_type":"article","og_title":"A SQL Server DBA myth a day: (15\/30) checkpoint only writes pages from committed transactions - Paul S. Randal","og_description":"(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) My DBA Mythbusters session went *really* well at SQL Connections [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/","og_site_name":"Paul S. Randal","article_published_time":"2010-04-15T13:05:00+00:00","article_modified_time":"2017-07-20T15:13:35+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/","name":"A SQL Server DBA myth a day: (15\/30) checkpoint only writes pages from committed transactions - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-04-15T13:05:00+00:00","dateModified":"2017-07-20T15:13:35+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"A SQL Server DBA myth a day: (15\/30) checkpoint only writes pages from committed transactions"}]},{"@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\/716","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=716"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/716\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=716"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=716"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=716"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}