{"id":874,"date":"2009-05-04T16:04:00","date_gmt":"2009-05-04T16:04:00","guid":{"rendered":"\/blogs\/paul\/post\/Do-transactions-rollback-when-DBCC-CHECKDB-runs.aspx"},"modified":"2013-05-20T15:10:58","modified_gmt":"2013-05-20T22:10:58","slug":"do-transactions-rollback-when-dbcc-checkdb-runs","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/do-transactions-rollback-when-dbcc-checkdb-runs\/","title":{"rendered":"Do transactions rollback when DBCC CHECKDB runs?"},"content":{"rendered":"

Recently there’s been a spate of people noticing strange behavior from active transactions when DBCC CHECKDB<\/span> (or any of the other DBCC<\/span> consistency checking commands run).<\/span><\/p>\n

For example, I’ve create a database call DbccTest<\/span> with a single table. In one connection I do:<\/span><\/p>\n

BEGIN TRAN
\nINSERT INTO t1 VALUES (1, 1);
\nGO
\n<\/span><\/span><\/p><\/blockquote>\n

And in another connection I do:<\/span><\/p>\n

DBCC CHECKDB (DbccTest) WITH ALL_ERRORMSGS, NO_INFOMSGS;
\nGO<\/span>\u00a0<\/span><\/p><\/blockquote>\n

Look what gets printed in the error log:<\/span><\/p>\n

2009-05-04 16:03:21.55 spid54\u00a0\u00a0\u00a0\u00a0\u00a0 1 transactions rolled back in database ‘DbccTest’ (14)<\/strong>. This is an informational message only. No user action is required.
\n2009-05-04 16:03:21.91 spid54\u00a0\u00a0\u00a0\u00a0\u00a0 DBCC CHECKDB (DbccTest) WITH all_errormsgs, no_infomsgs executed by ROADRUNNERPR\\paul found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.<\/span><\/p><\/blockquote>\n

I’ve highlighted the weird part in bold – it looks like crash recovery ran on the DbccTest<\/span> database. What’s going on?<\/span><\/p>\n

Well, crash recovery *DID* run on the DbccTest<\/span> database – only it ran crash recovery into a hidden database snapshot. In the first part of the very long (well, 13 pages – not as long as the 70 page description in the 2008 internals book) post on how DBCC CHECKDB<\/span> works (see <\/span>CHECKDB From Every Angle: Complete description of all CHECKDB stages<\/span><\/a>), I explain why DBCC CHECKDB<\/span> needs a transactionally-consistent, point-in-time view of the database to run consistency checks on. In 2005 I changed the code to use a database snapshot – which by it’s very nature provides a point-in-time, transactionally-consistent view of the database. When\u00a0a database snapshot is created on a database, the transaction log of that database is examined, and crash-recovery is run on it, but *into the database snapshot* – the source database is totally unaffected. The message in the error log is from DBCC CHECKDB<\/span>‘s hidden database snapshot starting up – but it’s pretty misleading, I must admit.<\/span><\/p>\n

The database ID in parentheses is the database ID of the database snapshot, not the actual database – further confusing things.<\/span><\/p>\n

One more twist to this behavior is that if there is a very long-running transaction in the database being consistency-checked, the database snapshot creation could take hours (because it has to wait for the long-running transaction to rollback, in the snapshot). If you think that the consistency check has hung, and try to kill it, you won’t be able to and the SPID will appear as if it’s in rollback. This is confusing because DBCC CHECKB<\/span> doesn’t do anything – so shouldn’t have anything to rollback – but it’s the database snapshot creation that’s the problem. Once crash recovery has started on the database snapshot, it can’t be interrupted – so you have to wait for it to finish (and the database snapshot to be created) before the DBCC<\/span> command will actually stop, and remove the database snapshot. It’s a weird side-effect, but a necessary one unfortunately. The recovery code could be changed to check for attention signals every so often I suppose, but I’m not holding-my-breath for that change.<\/span><\/p>\n

Hope this helps.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Recently there’s been a spate of people noticing strange behavior from active transactions when DBCC CHECKDB (or any of the other DBCC consistency checking commands run). For example, I’ve create a database call DbccTest with a single table. In one connection I do: BEGIN TRAN INSERT INTO t1 VALUES (1, 1); GO And in another […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,31,33,34,52],"tags":[],"class_list":["post-874","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle","category-database-maintenance","category-database-snapshots","category-dbcc","category-involuntary-dba"],"yoast_head":"\nDo transactions rollback when DBCC CHECKDB runs? - 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\/do-transactions-rollback-when-dbcc-checkdb-runs\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Do transactions rollback when DBCC CHECKDB runs? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Recently there’s been a spate of people noticing strange behavior from active transactions when DBCC CHECKDB (or any of the other DBCC consistency checking commands run). For example, I’ve create a database call DbccTest with a single table. In one connection I do: BEGIN TRAN INSERT INTO t1 VALUES (1, 1); GO And in another […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/do-transactions-rollback-when-dbcc-checkdb-runs\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-05-04T16:04:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-05-20T22:10:58+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\/do-transactions-rollback-when-dbcc-checkdb-runs\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/do-transactions-rollback-when-dbcc-checkdb-runs\/\",\"name\":\"Do transactions rollback when DBCC CHECKDB runs? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-05-04T16:04:00+00:00\",\"dateModified\":\"2013-05-20T22:10:58+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/do-transactions-rollback-when-dbcc-checkdb-runs\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/do-transactions-rollback-when-dbcc-checkdb-runs\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/do-transactions-rollback-when-dbcc-checkdb-runs\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Do transactions rollback when DBCC CHECKDB runs?\"}]},{\"@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":"Do transactions rollback when DBCC CHECKDB runs? - 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\/do-transactions-rollback-when-dbcc-checkdb-runs\/","og_locale":"en_US","og_type":"article","og_title":"Do transactions rollback when DBCC CHECKDB runs? - Paul S. Randal","og_description":"Recently there’s been a spate of people noticing strange behavior from active transactions when DBCC CHECKDB (or any of the other DBCC consistency checking commands run). For example, I’ve create a database call DbccTest with a single table. In one connection I do: BEGIN TRAN INSERT INTO t1 VALUES (1, 1); GO And in another […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/do-transactions-rollback-when-dbcc-checkdb-runs\/","og_site_name":"Paul S. Randal","article_published_time":"2009-05-04T16:04:00+00:00","article_modified_time":"2013-05-20T22:10:58+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\/do-transactions-rollback-when-dbcc-checkdb-runs\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/do-transactions-rollback-when-dbcc-checkdb-runs\/","name":"Do transactions rollback when DBCC CHECKDB runs? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-05-04T16:04:00+00:00","dateModified":"2013-05-20T22:10:58+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/do-transactions-rollback-when-dbcc-checkdb-runs\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/do-transactions-rollback-when-dbcc-checkdb-runs\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/do-transactions-rollback-when-dbcc-checkdb-runs\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Do transactions rollback when DBCC CHECKDB runs?"}]},{"@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\/874","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=874"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/874\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=874"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=874"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=874"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}