Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION

Introduction

There 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

Database Snapshot

A 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.

The 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.

Waiting for the Snapshot

Because 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.)

During 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 value of DATABASE_SNAPSHOT_CREATION.

The 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 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.

Summary

SQL Server will return a log_reuse_wait_desc value of DATABASE_SNAPSHOT_CREATION 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.

Log Reuse Wait Series

This post is part of a short series about circumstances that prevent transaction log truncation.
Below is a list of links to the posts that are already available.