{"id":1850,"date":"2014-01-20T11:00:35","date_gmt":"2014-01-20T16:00:35","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1850"},"modified":"2014-11-13T13:43:31","modified_gmt":"2014-11-13T18:43:31","slug":"log-reuse-waits-explained-database_snapshot_creation","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/","title":{"rendered":"Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nThere are eight reasons SQL Server might report when it cannot truncate the transaction log. Any one of these reasons results in a growing log file. This short series is looking at each of them in detail, explaining what is causing it and what you can do to resolve it. Today's log reuse wait reason is: DATABASE_SNAPSHOT_CREATION<\/span>\n<\/p>\n

Database Snapshot<\/h3>\n

\nA database snapshot is a point in time image of a database. It is implemented as a shadow file. That means that only the pages that have changed since that snapshot was created are actually stored in the snapshot file; the original unchanged version of those pages that is. All the other pages are read directly from the database files. From the creation on, every time a page in the database changes the original version is first moved over to the snapshot. That way SQL Server can provide that point in time image of the database.\n<\/p>\n

\nThe creation of the snapshot is a not very resource intensive and usually takes only a few seconds. However, during this time SQL Server needs to keep an eye on concurrent changes. Not surprising, it uses the transaction log for that.\n<\/p>\n

Waiting for the Snapshot<\/h3>\n

\nBecause SQL Server must ensure that the snapshot in itself is transactionally consistent, it uses the database transaction log at the end of the snapshot creation process to apply changes that were committed since and undo changes that happened during the snapshot creation but where not committed at the time the snapshot creation finished. (All those actions are only applied to the snapshot file; the transactions in the actual database are not affected.)\n<\/p>\n

\nDuring the creation process, SQL Server can therefore not truncate the log. If it runs out of log space because of that it will return a log_reuse_wait_desc<\/span> value of DATABASE_SNAPSHOT_CREATION<\/span>.\n<\/p>\n

\nThe process of creating a snapshot is usually very quick and you should not see this wait for a long time. If you are not using snapshots yourself however you should be aware that SQL Server is making use of this technology for internal purposes. A prominent example is the DBCC CHECKDB<\/span> command. It will create a database snapshot to run its checks against. So you might see the above mentioned wait when executing your database consistency checks. But again, the actual wait time should always be fairly short.\n<\/p>\n

Summary<\/h3>\n

\nSQL Server will return a log_reuse_wait_desc<\/span> value of DATABASE_SNAPSHOT_CREATION<\/span> if it runs out of virtual log files during the creation of a database snapshot. As that is usually a quick process you should not see this wait for a long time. Be aware, that SQL Server is using this feature internally, so you might see this log reuse wait type even if you are not using database snapshots yourself.\n<\/p>\n

Log Reuse Wait Series<\/h3>\n

\nThis post is part of a short series about circumstances that prevent transaction log truncation.
\nBelow is a list of links to the posts that are already available.\n<\/p>\n

\n
\n

Eight Reasons why your Transaction Log Files keep growing<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: CHECKPOINT<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: LOG_BACKUP<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: ACTIVE_TRANSACTION<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: DATABASE_MIRRORING<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: REPLICATION<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: LOG_SCAN<\/a><\/h2>\n
<\/div>\n<\/div>\n<\/div>\n\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Is your database log file growing out of bound and reporting DATABASE_SNAPSHOT_CREATION as reason? Read on to find out what that means and what you can do about it.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5,61,27,62],"tags":[80,15,63,64],"class_list":["post-1850","post","type-post","status-publish","format-standard","hentry","category-general","category-log-reuse-wait","category-series","category-transaction-log","tag-database-snapshot","tag-sql-server","tag-transaction-log-2","tag-transaction-log-reuse"],"acf":[],"yoast_head":"\nLog Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION - sqlity.net<\/title>\n<meta name=\"description\" content=\"Is your database log file growing out of bound and reporting DATABASE_SNAPSHOT_CREATION as reason? Read on to find out what that means and what you can do about it.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Is your database log file growing out of bound and reporting DATABASE_SNAPSHOT_CREATION as reason? Read on to find out what that means and what you can do about it.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-20T16:00:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:43:31+00:00\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\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\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1850\\\/log-reuse-waits-explained-database_snapshot_creation\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1850\\\/log-reuse-waits-explained-database_snapshot_creation\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION\",\"datePublished\":\"2014-01-20T16:00:35+00:00\",\"dateModified\":\"2014-11-13T18:43:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1850\\\/log-reuse-waits-explained-database_snapshot_creation\\\/\"},\"wordCount\":557,\"commentCount\":0,\"keywords\":[\"database snapshot\",\"SQL Server\",\"transaction log\",\"transaction log reuse\"],\"articleSection\":[\"General\",\"Log Reuse Wait\",\"Series\",\"Transaction Log\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1850\\\/log-reuse-waits-explained-database_snapshot_creation\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1850\\\/log-reuse-waits-explained-database_snapshot_creation\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1850\\\/log-reuse-waits-explained-database_snapshot_creation\\\/\",\"name\":\"Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2014-01-20T16:00:35+00:00\",\"dateModified\":\"2014-11-13T18:43:31+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Is your database log file growing out of bound and reporting DATABASE_SNAPSHOT_CREATION as reason? Read on to find out what that means and what you can do about it.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1850\\\/log-reuse-waits-explained-database_snapshot_creation\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1850\\\/log-reuse-waits-explained-database_snapshot_creation\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1850\\\/log-reuse-waits-explained-database_snapshot_creation\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION - sqlity.net","description":"Is your database log file growing out of bound and reporting DATABASE_SNAPSHOT_CREATION as reason? Read on to find out what that means and what you can do about it.","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:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/","og_locale":"en_US","og_type":"article","og_title":"Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION - sqlity.net","og_description":"Is your database log file growing out of bound and reporting DATABASE_SNAPSHOT_CREATION as reason? Read on to find out what that means and what you can do about it.","og_url":"https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-01-20T16:00:35+00:00","article_modified_time":"2014-11-13T18:43:31+00:00","author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION","datePublished":"2014-01-20T16:00:35+00:00","dateModified":"2014-11-13T18:43:31+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/"},"wordCount":557,"commentCount":0,"keywords":["database snapshot","SQL Server","transaction log","transaction log reuse"],"articleSection":["General","Log Reuse Wait","Series","Transaction Log"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/","url":"https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/","name":"Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2014-01-20T16:00:35+00:00","dateModified":"2014-11-13T18:43:31+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Is your database log file growing out of bound and reporting DATABASE_SNAPSHOT_CREATION as reason? Read on to find out what that means and what you can do about it.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1850\/log-reuse-waits-explained-database_snapshot_creation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-tQ","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1850","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1850"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1850\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1850"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1850"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1850"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}