{"id":757,"date":"2010-01-07T11:03:00","date_gmt":"2010-01-07T11:03:00","guid":{"rendered":"\/blogs\/paul\/post\/Misconception-around-database-snapshots-and-transaction-rollbacks.aspx"},"modified":"2017-04-13T09:51:39","modified_gmt":"2017-04-13T16:51:39","slug":"misconceptions-around-database-snapshots-and-transaction-rollbacks","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/","title":{"rendered":"Misconceptions around database snapshots and transaction rollbacks"},"content":{"rendered":"

\nThis is a quick post to clarify an <\/font>article<\/font><\/a> I saw on SQLServerCentral this morning that seemed to state that transaction rollbacks push data into database snapshots. This is absolutely not true.<\/font>\n<\/p>\n

\nA database page is copied into a database snapshot before it is changed in the source database. Although the mechanism is commonly called copy-on-write, it's more technically accurate to call it copy-before-write (but this makes it a bit harder to understand for many people). Once a page has been copied into the database snapshot, it is never removed from the database snapshot, and won't ever be copied into it again, as the database snapshot already has the correct point-in-time copy of the updated page. (For more info on database snapshots in general, see the Books Online entry Database Snapshots<\/a>.)<\/font>\n<\/p>\n

\nA transaction makes one or more changes to the database, updating one or more pages. These pages will be copied into the database snapshot if they're not already there, so the pre-change image (that existed at the time the database snapshot was created) is preserved.<\/font>\n<\/p>\n

\nIf the transaction rolls back, the rollback occurs by generating the reverse operations that the transaction performed and applying them to the database (e.g. an insert will be rolled back by the generation and application of a delete; an update will be rolled back by replacing the updated parts of the record with the pre-update values). I'll explain more about this in a future blog post.<\/font>\n<\/p>\n

\nThese rollback operations will occur on the same pages that the initial transaction operations occured on. This means that no other pages will be changed by the rollback operations and so no further pages will be copied into the database snapshot by a rollback. Pages can't be removed from the database snapshot when the transaction rolls back because they have still changed in the source database (although the net effect of the transaction+rollback is no logical changes to the data, the page headers will have changed to have an updated Log Sequence Number on), and so the copy in the database snapshot is still required to preserve the point-in-time view of the database (at the physical level) as of the time the database snapshot was created.<\/font>\n<\/p>\n

\nI'll prove this to you with a simple script that you can play around with to convince yourself also.<\/font>\n<\/p>\n

\n

\n\tUSE master;
\n\tGO<\/font>\n\t<\/p>\n

\n\tDROP DATABASE SnapRollbackTest_Snapshot;
\n\tGO
\n\tDROP DATABASE SnapRollbackTest;
\n\tGO<\/font>\n\t<\/p>\n

\n\tCREATE DATABASE SnapRollbackTest;
\n\tGO
\n\tUSE SnapRollbackTest;
\n\tGO<\/font>\n\t<\/p>\n

\n\tCREATE TABLE MyTable (c1 INT);
\n\tCREATE CLUSTERED INDEX MyTable_CL ON MyTable (c1);
\n\tGO<\/font>\n\t<\/p>\n

\n\tSET NOCOUNT ON;
\n\tGO<\/font>\n\t<\/p>\n

\n\tDECLARE @a INT;
\n\tSELECT @a = 1;
\n\tWHILE (@a < 100001)
\n\tBEGIN
\n\t    INSERT INTO MyTable (c1) VALUES (@a);
\n\t    SELECT @a = @a + 1;
\n\tEND;
\n\tGO<\/font>\n\t<\/p>\n

\n\tCREATE DATABASE SnapRollbackTest_Snapshot ON
\n\t    (NAME = N'SnapRollbackTest', FILENAME = N'C:\\SQLskills\\test\\SnapRollbackTest.mdfss')
\n\tAS SNAPSHOT OF SnapRollbackTest;
\n\tGO<\/font>\n\t<\/p>\n

\n\t— Initial size
\n\tSELECT size_on_disk_bytes AS [Initial Size (bytes)] FROM sys.dm_io_virtual_file_stats (DB_ID ('SnapRollbackTest_Snapshot'), 1);
\n\tGO<\/font>\n\t<\/p>\n

\n\t— Start transaction
\n\tBEGIN TRAN
\n\tGO
\n\tUPDATE MyTable SET c1 = 42;
\n\tGO
\n\tCHECKPOINT; — to make sure absolutely everything is flushed to disk
\n\tGO<\/font>\n\t<\/p>\n

\n\tSELECT size_on_disk_bytes AS [After Transaction (bytes)] FROM sys.dm_io_virtual_file_stats (DB_ID ('SnapRollbackTest_Snapshot'), 1);
\n\tGO<\/font>\n\t<\/p>\n

\n\t— Rollback
\n\tROLLBACK TRAN;
\n\tGO<\/font>\n\t<\/p>\n

\n\tSELECT size_on_disk_bytes AS [After Rollback (bytes)] FROM sys.dm_io_virtual_file_stats (DB_ID ('SnapRollbackTest_Snapshot'), 1);
\n\tGO<\/font>\n\t<\/p>\n<\/blockquote>\n

\n

\n\tInitial Size (bytes)
\n\t——————–
\n\t196608<\/font>\n\t<\/p>\n

\n\tAfter Transaction (bytes)
\n\t————————-
\n\t1835008<\/font>\n\t<\/p>\n

\n\tAfter Rollback (bytes)
\n\t———————-
\n\t1835008<\/font>\n\t<\/p>\n<\/blockquote>\n

\nYou can clearly see that the size of the database snapshot did NOT increase at all because of the transaction rollback. Using the script above you can try this using a heap, clustered index, various combinations of row size and number of rows – the result will be the same – the database snapshot will not increase in size because of a transaction rollback. I tried a bunch of different combinations, all with the same result.<\/font>\n<\/p>\n

\nIn the back of my head there's a niggly feeling that there's a funky, rare, pathalogical case where some weird combination of operations results in a page split when rolled-back, but I can't engineer it.<\/font>\n<\/p>\n

\nBottom line – transaction rollbacks do not cause the database snapshot to increase in size, as the rollback operates on the database pages that have already been copied into the snapshot because they changed due to the operations of the transaction itself.<\/font>\n<\/p>\n

\nHope this helps!<\/font>\n<\/p>\n

\nPS As one of the commenters pointed out, the initial snapshot size can be affected by the crash-recovery that is run when the snapshot is created. I go into details on that process in this post<\/a> as it can be confusing when CHECKDB runs.<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

This is a quick post to clarify an article I saw on SQLServerCentral this morning that seemed to state that transaction rollbacks push data into database snapshots. This is absolutely not true. A database page is copied into a database snapshot before it is changed in the source database. Although the mechanism is commonly called copy-on-write, […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[33,61],"tags":[],"class_list":["post-757","post","type-post","status-publish","format-standard","hentry","category-database-snapshots","category-misconceptions"],"yoast_head":"\nMisconceptions around database snapshots and transaction rollbacks - 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\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Misconceptions around database snapshots and transaction rollbacks - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This is a quick post to clarify an article I saw on SQLServerCentral this morning that seemed to state that transaction rollbacks push data into database snapshots. This is absolutely not true. A database page is copied into a database snapshot before it is changed in the source database. Although the mechanism is commonly called copy-on-write, […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-01-07T11:03:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:51:39+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=\"4 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\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/\",\"name\":\"Misconceptions around database snapshots and transaction rollbacks - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-01-07T11:03:00+00:00\",\"dateModified\":\"2017-04-13T16:51:39+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Misconceptions around database snapshots and transaction rollbacks\"}]},{\"@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":"Misconceptions around database snapshots and transaction rollbacks - 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\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/","og_locale":"en_US","og_type":"article","og_title":"Misconceptions around database snapshots and transaction rollbacks - Paul S. Randal","og_description":"This is a quick post to clarify an article I saw on SQLServerCentral this morning that seemed to state that transaction rollbacks push data into database snapshots. This is absolutely not true. A database page is copied into a database snapshot before it is changed in the source database. Although the mechanism is commonly called copy-on-write, […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/","og_site_name":"Paul S. Randal","article_published_time":"2010-01-07T11:03:00+00:00","article_modified_time":"2017-04-13T16:51:39+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/","name":"Misconceptions around database snapshots and transaction rollbacks - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-01-07T11:03:00+00:00","dateModified":"2017-04-13T16:51:39+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-snapshots-and-transaction-rollbacks\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Misconceptions around database snapshots and transaction rollbacks"}]},{"@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\/757","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=757"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/757\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=757"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=757"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=757"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}