{"id":1092,"date":"2008-01-30T04:26:11","date_gmt":"2008-01-30T04:26:11","guid":{"rendered":"\/blogs\/paul\/post\/Database-snapshots-when-things-go-wrong.aspx"},"modified":"2014-06-19T10:02:36","modified_gmt":"2014-06-19T17:02:36","slug":"database-snapshots-when-things-go-wrong","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/","title":{"rendered":"Database snapshots &#8211; when things go wrong"},"content":{"rendered":"<p>This is a post I&#8217;ve been meaning to do for a while &#8211; detailing some of the problems you can run into when using database snapshots.<\/p>\n<p><strong>Reverting to a snapshot<\/strong><\/p>\n<p>Many people use snapshots as a way of protecting against mistakes during a complicated set of changes to a database &#8211; you can just revert to the snapshot if something goes wrong. However, reverting to a snapshot has a little-known (but documented) problem. Let&#8217;s see. I&#8217;m going to create a database plus a snapshot of it and then start taking backups.<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nCREATE DATABASE &#x5B;SnapshotTest];\r\nGO\r\nALTER DATABASE &#x5B;SnapshotTest] SET RECOVERY FULL;\r\nGO\r\nBACKUP DATABASE &#x5B;SnapshotTest] TO DISK = N'C:\\SQLskills\\SnapshotTest.bak' WITH INIT;\r\nGO\r\nCREATE TABLE &#x5B;SnapshotTest].&#x5B;dbo].&#x5B;MyTable] (&#x5B;c1] INT);\r\nGO\r\n\r\nBACKUP LOG &#x5B;SnapshotTest] TO DISK = N'C:\\SQLskills\\SnapshotTest_log.bak' WITH INIT;\r\nGO\r\n\r\n-- Imagine a bunch of things happen here\r\n-- Create the snapshot database, by first checking which files exist\r\nSELECT * FROM &#x5B;SnapshotTest].&#x5B;sys].&#x5B;database_files];\r\nGO\r\nCREATE DATABASE &#x5B;ST_Snap] ON\r\n(NAME = N'SnapshotTest', FILENAME = N'C:\\SQLskills\\SnapshotTest_snap.snp')\r\nAS SNAPSHOT OF &#x5B;SnapshotTest];\r\nGO\r\n<\/pre>\n<p>Now I&#8217;m going to do some stuff that&#8217;s a mistake:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDROP TABLE &#x5B;SnapshotTest].&#x5B;dbo].&#x5B;MyTable];\r\nGO\r\n<\/pre>\n<p>Not a problem as I can revert to my database snapshot:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nRESTORE DATABASE &#x5B;SnapshotTest] FROM DATABASE_SNAPSHOT = N'ST_Snap';\r\nGO\r\n<\/pre>\n<p>Oops! I forgot to take a log backup to capture everything that happened since the last log backup&#8230;<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nBACKUP LOG &#x5B;SnapshotTest] TO DISK = N'C:\\SQLskills\\SnapshotTest_log.bak';\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nMsg 4214, Level 16, State 1, Line 1\r\nBACKUP LOG cannot be performed because there is no current database backup.\r\nMsg 3013, Level 16, State 1, Line 1\r\nBACKUP LOG is terminating abnormally.\r\n<\/pre>\n<p>Reverting to a snapshot rebuilds the transaction log and breaks the log backup chain. The only thing I can do is take a full or differential backup and <em>then<\/em> start taking log backups again. So &#8211; I lost the ability to do point-in-time restores in the period from the last log backup to the time when I reverted to the snapshot.<\/p>\n<p>So how can I tell when the database was reverted? Books Online documents that the <em>restorehistory<\/em> table in <em>msdb<\/em> should have an entry with <em>restore_type = &#8216;R&#8217;<\/em>. Let&#8217;s try:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nSELECT * FROM &#x5B;msdb].&#x5B;dbo].&#x5B;restorehistory] WHERE &#x5B;destination_database_name] = N'SnapshotTest';\r\nGO\r\n<\/pre>\n<p>Nope &#8211; nothing. That functionality seems to be broken. The only way I could find to trace reverting was in the error log:<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n2008-01-30 11:09:21.73 spid53\u00a0\u00a0\u00a0\u00a0\u00a0 Reverting database 'SnapshotTest' to the point in time of database snapshot 'ST_Snap' with split point LSN 26000000013800001 (0x0000001a:0000008a:0001). This is an informational message only. No user action is required.\r\n2008-01-30 11:09:21.74 spid53\u00a0\u00a0\u00a0\u00a0\u00a0 Starting up database 'SnapshotTest'.\r\n2008-01-30 11:09:21.74 spid53\u00a0\u00a0\u00a0\u00a0\u00a0 The database 'SnapshotTest' is marked RESTORING and is in a state that does not allow recovery to be run.\r\n2008-01-30 11:09:21.85 spid53\u00a0\u00a0\u00a0\u00a0\u00a0 Starting up database 'SnapshotTest'.\r\n2008-01-30 11:09:21.87 spid53\u00a0\u00a0\u00a0\u00a0\u00a0 Starting up database 'ST_Snap'.\r\n<\/pre>\n<p>Note that the snapshot database is still there and continues to work.<\/p>\n<p><strong>Running out of space in a snapshot<\/strong><\/p>\n<p>The next problem that can happen is if you create a database snapshot on a volume that doesn&#8217;t have much disk space. In that case you can actually run out of space and the snapshot will go suspect and be unusable until it&#8217;s dropped and recreated. Let&#8217;s see what happens:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n-- Create snapshot of large database on a volume with not much space\r\nSELECT * FROM &#x5B;SalesDB].&#x5B;sys].&#x5B;database_files];\r\nGO\r\nCREATE DATABASE &#x5B;SalesDB_Snap] ON\r\n(NAME = N'SalesDBData', FILENAME = N'D:\\sqlskills\\SalesDB_snap.snp')\r\nAS SNAPSHOT OF &#x5B;SalesDB];\r\nGO\r\nALTER INDEX ALL ON &#x5B;SalesDB].&#x5B;dbo].&#x5B;Sales] REBUILD;\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nMsg 5128, Level 17, State 2, Line 1\r\nWrite to sparse file 'D:\\sqlskills\\SalesDB_snap.snp' failed due to lack of disk space.\r\n<\/pre>\n<p>The query that causes the sparse file to run out of space doesn&#8217;t fail, but the user running the query will see this error. Note that it doesn&#8217;t say the snapshot has gone suspect. If you try to use it then it will tell you, plus there info written to the error log:<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n2008-01-30 11:50:29.14 spid53\u00a0\u00a0\u00a0\u00a0\u00a0 Error: 17053, Severity: 16, State: 1.\r\n2008-01-30 11:50:29.14 spid53\u00a0\u00a0\u00a0\u00a0\u00a0 D:\\sqlskills\\SalesDB_snap.snp: Operating system error 112(There is not enough space on the disk.) encountered.\r\n2008-01-30 11:50:29.15 spid53\u00a0\u00a0\u00a0\u00a0\u00a0 Error: 3420, Severity: 21, State: 1.\r\n2008-01-30 11:50:29.15 spid53\u00a0\u00a0\u00a0\u00a0\u00a0 Database snapshot 'SalesDB_Snap' has failed an IO operation and is marked suspect.\u00a0 It must be dropped and recreated.\r\n<\/pre>\n<p><strong>Running out of space while running <em>DBCC CHECKDB<\/em><\/strong><\/p>\n<p>One thing that isn&#8217;t common knowledge is that <em>DBCC CHECKDB<\/em> uses database snapshots as it&#8217;s mechanism for running online in SQL Server 2005. You can&#8217;t control where the snapshot is created (it&#8217;s actually created in the same location as the files comprising the database being checked &#8211; see my previous post on the <em>DBCC CHECKDB<\/em> stages <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-complete-description-of-all-checkdb-stages\/\">here<\/a> for more info) but you can create your own snapshot and check that. Anyway &#8211; it&#8217;s possible to run out of space in the snapshot while <em>DBCC CHECKDB<\/em> is running.\u00a0I setup a situation with a large database on a drive with only 1MB of space left, started a large index rebuild in the database and then tried to run <em>DBCC CHECKDB<\/em>. Let&#8217;s see what happened:<\/p>\n<p>The index rebuild statement:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nALTER INDEX ALL ON &#x5B;SalesDB2].&#x5B;dbo].&#x5B;Sales] REBUILD;\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nMsg 5128, Level 17, State 2, Line 1\r\nWrite to sparse file 'd:\\sqlskills\\salesdbdata.mdf:MSSQL_DBCC20' failed due to lack of disk space.\r\n<\/pre>\n<p>So some poor unsuspecting user gets this error and has no idea why. For DBAs its a bit obscure too. The syntax is describing an <em>alternate stream<\/em> on the existing data file and the snapshot in question can&#8217;t be accessed at all.<\/p>\n<p>The DBCC CHECKDB statement:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDBCC CHECKDB (N'salesdb2') WITH NO_INFOMSGS;\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nMsg 1823, Level 16, State 1, Line 1\r\nA database snapshot cannot be created because it failed to start.\r\nMsg 1823, Level 16, State 2, Line 1\r\nA database snapshot cannot be created because it failed to start.\r\nMsg 7928, Level 16, State 1, Line 1\r\nThe database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.\r\nMsg 5128, Level 17, State 2, Line 1\r\nWrite to sparse file 'd:\\sqlskills\\salesdbdata.mdf:MSSQL_DBCC20' failed due to lack of disk space.\r\nMsg 3313, Level 21, State 2, Line 1\r\nDuring redoing of a logged operation in database 'salesdb2', an error occurred at log record ID (1628:252:1). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.\r\nMsg 0, Level 20, State 0, Line 0\r\nA severe error occurred on the current command.  The results, if any, should be discarded.\r\n<\/pre>\n<p>Wow &#8211; major amounts of errors &#8211; and it all boils down to running out of space.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is a post I&#8217;ve been meaning to do for a while &#8211; detailing some of the problems you can run into when using database snapshots. Reverting to a snapshot Many people use snapshots as a way of protecting against mistakes during a complicated set of changes to a database &#8211; you can just revert [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[33,38],"tags":[],"class_list":["post-1092","post","type-post","status-publish","format-standard","hentry","category-database-snapshots","category-example-scripts"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Database snapshots - when things go wrong - 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\/database-snapshots-when-things-go-wrong\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Database snapshots - when things go wrong - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This is a post I&#8217;ve been meaning to do for a while &#8211; detailing some of the problems you can run into when using database snapshots. Reverting to a snapshot Many people use snapshots as a way of protecting against mistakes during a complicated set of changes to a database &#8211; you can just revert [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-01-30T04:26:11+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-06-19T17:02:36+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=\"6 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\/database-snapshots-when-things-go-wrong\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/\",\"name\":\"Database snapshots - when things go wrong - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-01-30T04:26:11+00:00\",\"dateModified\":\"2014-06-19T17:02:36+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Database snapshots &#8211; when things go wrong\"}]},{\"@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":"Database snapshots - when things go wrong - 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\/database-snapshots-when-things-go-wrong\/","og_locale":"en_US","og_type":"article","og_title":"Database snapshots - when things go wrong - Paul S. Randal","og_description":"This is a post I&#8217;ve been meaning to do for a while &#8211; detailing some of the problems you can run into when using database snapshots. Reverting to a snapshot Many people use snapshots as a way of protecting against mistakes during a complicated set of changes to a database &#8211; you can just revert [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/","og_site_name":"Paul S. Randal","article_published_time":"2008-01-30T04:26:11+00:00","article_modified_time":"2014-06-19T17:02:36+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/","name":"Database snapshots - when things go wrong - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-01-30T04:26:11+00:00","dateModified":"2014-06-19T17:02:36+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Database snapshots &#8211; when things go wrong"}]},{"@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\/1092","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=1092"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1092\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1092"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1092"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1092"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}