Categories
DBA

How Database Snapshot Works

Its essential that a DBA understands what happens when a snapshot is created. The following process happens,

Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created.

To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. As data is written to the sparse file, NTFS allocates disk space gradually. Potentially, a sparse file can grow very large. The source database, however, is not affected; actions on it continue normally. Sparse files grow in 64-kilobyte (KB) increments; thus, the size of a sparse file on disk is always a multiple of 64 KB.

This is illustrated with the help of this diagram:

1. At first when the snapshot is created, the sparse file is empty.

snapshot_working_1

2. When the first update operation happens, the original data is copied into the sparse file.

The database engine then updates the data in the source database. This process is called Copy-on-write-operation.

snapshot_working_2

During the read operation on the snapshot, the original data is read from the source database. For the data which has been changed, the snapshots read from the sparse file.

If any uncommitted transactions are in progress in the database @ the time when the snapshot was created, they will not be reflected in the snapshot.

Disk Space consumed by Sparse File :

You can find this by two methods,

1.Right click the file and select properties and choose general tab and view the size on disk which is 48 kb. Refer the below figure,

snapshot_working_3

2. Another method is using fn_virtualfilestats function as follows,

Here I specified Null for the function fn_virtualfilestats in order to obtain information for all the files in the database. The BytesonDisk column will return the disk space consumed by sparse file in bytes. Here it is 49152 bytes = 49152/8192 = 6 Pages=48 kb.Compare the output of 1 and 2 both will same as 48 kb.

snapshot_working_4

The maximum size to which a sparse can grow is the size of the corresponding source database file at the time of the snapshot creation. You can view this by two methods,

  1. Right click the file and select properties and choose general tab and view the size value which is 6 Mb (6291456 bytes).
  2. You can make use of the catalog view sys.master_files and view the size column it will be in pages. In my case it is 768 pages = 768*8192 = 6291456 bytes. On comparing the output of 1 and 2 both are same as 6291456 bytes.

snapshot_working_5

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*