{"id":5061,"date":"2020-01-15T13:38:28","date_gmt":"2020-01-15T21:38:28","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=5061"},"modified":"2020-01-15T13:38:28","modified_gmt":"2020-01-15T21:38:28","slug":"the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/","title":{"rendered":"The Curious Case of… transactions rolling back during DBCC CHECKDB"},"content":{"rendered":"

(The Curious Case of\u2026<\/em>\u00a0used to be part of our bi-weekly\u00a0newsletter<\/a>\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)<\/p>\n

\"\"<\/a><\/p>\n

 <\/p>\n

Continuing the database snapshot theme from the previous\u00a0Curious Case post<\/a>, I had another question from someone who was concerned about transactions rolling back during DBCC CHECKDB<\/em>. They’d just noticed the messages in the error log saying that when DBCC CHECKDB<\/em> was executed, it was causing transactions to roll back in the database – and how could that possibly be allowed to happen? They said they panicked and stopped all DBCC CHECKDB<\/em> executions.<\/p>\n

 <\/p>\n

There’s no need to panic. The problem is actually a bug in the database snapshot code that’s been there since SQL Server 2005, where it reports the wrong database name.<\/p>\n

Let’s try this on SQL Server 2019. First I’ll create a simple database, cycle the error log, and start a transaction:<\/p>\n

\r\nUSE [master];\r\nGO\r\n\r\nIF DATABASEPROPERTYEX (N'Company_Snapshot', N'Version') > 0\r\nBEGIN\r\n\tDROP DATABASE [Company_Snapshot];\r\nEND\r\nGO\r\nIF DATABASEPROPERTYEX (N'Company', N'Version') > 0\r\nBEGIN\r\n\tALTER DATABASE [Company] SET SINGLE_USER\r\n\t\tWITH ROLLBACK IMMEDIATE;\r\n\tDROP DATABASE [Company];\r\nEND\r\nGO\r\n\r\n-- Create a database\r\nCREATE DATABASE [Company];\r\nGO\r\n\r\nUSE [Company];\r\nGO\r\n\r\nCREATE TABLE [t1] ([c1] int);\r\nGO\r\n\r\nEXEC sp_cycle_errorlog;\r\nGO\r\n\r\nBEGIN TRAN\r\nINSERT INTO [t1] VALUES (1);\r\nGO\r\n<\/pre>\n
Now in a separate window, I’ll create a database snapshot an examine the error log:<\/p>\n
\r\nCREATE DATABASE [Company_Snapshot]\r\nON (\r\n\tNAME = N'Company',\r\n\tFILENAME = N'C:\\SQLskills\\CompanyData.mdfss')\r\nAS SNAPSHOT OF [Company];\r\nGO\r\nEXEC xp_readerrorlog;\r\nGO\r\n<\/pre>\n
And the pertinent lines from the error log are:<\/p>\n
\r\n2020-01-15 13:29:30.740 spid58       1 transactions rolled back in database 'Company_Snapshot' (17:0). This is an informational message only. No user action is required.\r\n2020-01-15 13:29:30.740 spid58       Recovery is writing a checkpoint in database 'Company_Snapshot' (17). This is an informational message only. No user action is required.\r\n<\/pre>\n
This is correct; the transaction is being rolled back in the context of the database snapshot, to make it transactionally consistent.<\/p>\n
Now let’s try a DBCC CHECKDB<\/em>:<\/p>\n
\r\nDBCC CHECKDB (N'Company');\r\nGO\r\nEXEC xp_readerrorlog;\r\nGO\r\n<\/pre>\n
And the pertinent lines from the error log are:<\/p>\n
\r\n2020-01-15 13:31:52.710 spid58       1 transactions rolled back in database 'Company' (21:0). This is an informational message only. No user action is required.\r\n2020-01-15 13:31:52.900 spid58       DBCC CHECKDB (Company) executed by CRINAN\\Paul found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  Internal database snapshot has split point LSN = 00000024:00000087:0001 and first LSN = 00000024:0000003c:0001.\r\n<\/pre>\n
And this is incorrect, and the cause of the concern.<\/p>\n
The ‘bug’ is that the name for the internal database snapshot is chosen (by the Database Manager) to be the same as for the real database, and DBCC CHECKDB<\/em> has no control over that, so it looks like the rollbacks are happening in the real database. But if you look at the database ID in the message, you’ll see that it’s not the same as for the real Company<\/em> database (which is 5 in this case). So, if you’re ever concerned by a message like this, just look up the database ID of the database you’re running DBCC CHECKDB<\/em> against and you’ll see that things are fine.<\/p>\n","protected":false},"excerpt":{"rendered":"
(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)   Continuing the […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,33,115],"tags":[],"class_list":["post-5061","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle","category-database-snapshots","category-the-curious-case-of"],"yoast_head":"\nThe Curious Case of... transactions rolling back during DBCC CHECKDB - 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\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Curious Case of... transactions rolling back during DBCC CHECKDB - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)   Continuing the […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2020-01-15T21:38:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2020\/01\/Picture1-150x150.jpg\" \/>\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\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/\",\"name\":\"The Curious Case of... transactions rolling back during DBCC CHECKDB - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2020\/01\/Picture1-150x150.jpg\",\"datePublished\":\"2020-01-15T21:38:28+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2020\/01\/Picture1.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2020\/01\/Picture1.jpg\",\"width\":817,\"height\":617},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Curious Case of… transactions rolling back during DBCC CHECKDB\"}]},{\"@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":"The Curious Case of... transactions rolling back during DBCC CHECKDB - 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\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/","og_locale":"en_US","og_type":"article","og_title":"The Curious Case of... transactions rolling back during DBCC CHECKDB - Paul S. Randal","og_description":"(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)   Continuing the […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/","og_site_name":"Paul S. Randal","article_published_time":"2020-01-15T21:38:28+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2020\/01\/Picture1-150x150.jpg","type":"","width":"","height":""}],"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\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/","name":"The Curious Case of... transactions rolling back during DBCC CHECKDB - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2020\/01\/Picture1-150x150.jpg","datePublished":"2020-01-15T21:38:28+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2020\/01\/Picture1.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2020\/01\/Picture1.jpg","width":817,"height":617},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-transactions-rolling-back-during-dbcc-checkdb\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"The Curious Case of… transactions rolling back during DBCC CHECKDB"}]},{"@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\/5061","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=5061"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5061\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=5061"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=5061"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=5061"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}