Overview

I recently conducted some tests to double check the exact behavior of database snapshots when:

• Snapshots are created on volumes which are much smaller than the actual database size
• Metadata-only operations such as DROP TABLE are executed
• DDL operations execute on the main database and the snapshot runs out of disk space

Here is my testing methodology and the customary summary of observations.

Scenario

• Drive C: contains the original data file for database TestSS. Data file size = 1.95GB
• TestSS.dbo.T1 is a table which contains 1GB of data
• TestSS_1 is a snapshot which was created on TestSS after the 1GB data insert was completed.

Test Case A: Can we create a snapshot on volume smaller than the database size?

1. The snapshot TestSS_1 was created on volume T: which is 10MB in size.
2. Size on disk for snapshot at creation = 192KB
3. The snapshot creation succeeded without any errors.

If you are curious as to what I mean by ‘size on disk’, please refer to this snapshot:

It is a property of the NTFS sparse file that the actual space occupied on disk is much lesser initially than the ‘Size’ which is displayed for that file in Explorer.

Test Case B: Metadata-only operations and snapshot files

1. Next, I executed a DROP TABLE T1 on TestSS, which returned immediately without any errors. At this time the snapshot did not have a large ‘on disk size’ – just 1.48MB
2. At this time the data is accessible through the snapshot just fine. If you do a select count(1) from testSS..T1 it fails (as expected). So the main operation (DROP TABLE) worked just fine.

How is this possible? It turns out that certain operations such as TRUNCATE TABLE, DROP TABLE etc. directly operate on the IAM chain and possibly other internal allocation pages to efficiently ‘clean up’ data and metadata. In such cases the copy-on-write to the snapshot is deferred till such time that the actual data page and extent are touched.

Test Case 3: What effect does failure to write to the snapshot have on the main database?

1. Next, I reverted the database TestSS to the snapshot TestSS_1 and then dropped the TestSS_1 snapshot. How do you revert a database to a snapshot? You use the RESTORE DATABASE … command like this: RESTORE DATABASE TestSS FROM DATABASE_SNAPSHOT = TestSS_1
2. I recreated TestSS_1 immediately using the CREATE DATABASE … AS SNAPSHOT …
3. Next, I executed DELETE T1 to force a logged data operation and it executed for some time before finally returning these errors:

Msg 5128, Level 17, State 2, Line 1

Write to sparse file ‘t:Datatestss_1.ss’ failed due to lack of disk space.

Msg 5128, Level 17, State 2, Line 1

Write to sparse file ‘t:Datatestss_1.ss’ failed due to lack of disk space.

Msg 5128, Level 17, State 2, Line 1

Write to sparse file ‘t:Datatestss_1.ss’ failed due to lack of disk space.

Though, interestingly the main DELETE did not fail; all rows from the table T1 have been deleted. It is by-design that any failures to write to the snapshot are not going to affect the primary operation on the database.

Summary

So in conclusion this is what we can learn from the above. Most of it is logical when you think about it, but it is better to be sure than to guess!

• Database snapshots can most definitely be created on volumes which are much smaller than the original database size
• Metadata only operations initially do not affect the snapshot. Only when the underlying pages are really ‘cleaned up’ by subsequent system processes, will the copy of the affected pages be moved into the database snapshot’s sparse file
• Failures to write to the snapshot will never affect the original database on which the DDL is being executed

Additional Reading

• Please also refer to the excellent white paper on Database Snapshots Performance Considerations Under IO Intensive Workloads for some more information on snapshots and their effect on performance.
• Paul Randal’s blogs about snapshots.
• Good information on snapshot internals is at the SQL CAT Team website.