A complete insigt on SQL Server Snapshots

The snapshots are a great new feature of SQL Server 2005. They allow you to keep a static, read only copy of the data in your database without any admin overhead. It can be used for a static copy of the database, reporting database or for testing purpose. It creates the copy of the data files of your original database but without occupying the same amount of disk space. As and when the data is modified in the original database, it is going to copy that extent (a collection of 8 datapages) in to the snapshot file created, otherwise the read request are served from the original databases.

I tested the database snapshots and found some interesting results which I want to share with you. I used the sample AdventureWorks database shipped with SQL Server 2005. I used the following statement to create the database snapshot

CREATE DATABASE AdvWorks_Snaphot

ON

(

NAME = 'AdventureWorks_Data', FILENAME = 'C:\SnapFolder\Adv_Snap.ss'

)

AS SNAPSHOT OF AdventureWorks

If you have more than one data files in your database, just specify all the datafiles e.g.

CREATE DATABASE AdvWorks_Snaphot

ON

(

NAME = 'AdventureWorks_Data1', FILENAME = 'C:\SnapFolder\Adv_Snap1.ss'

),

(

NAME = 'AdventureWorks_Data2', FILENAME = 'C:\SnapFolder\Adv_Snap2.ss'

),

(

NAME = 'AdventureWorks_Data3', FILENAME = 'C:\SnapFolder\Adv_Snap3.ss'

)

AS SNAPSHOT OF AdventureWorks

Now if you look at the SIZE of this file in windows explorer, it is the same size as the original database file, which in our case is 191 MB. Now, if you look at the "SIZE ON DISK" value, it is very low. In my case it was just 2.12 MB. That means that the snapshot requires a very small fraction of the original database size.

Now, if you just modify one row in one of the tables, you will find that the "SIZE ON DISK" value has just increased by 64 KB, which is the size of an extent.

Now comes the interesting part. The disk that I used for this test had a very small amount of free space. I started deleting a lot of tables from the original database so that all the data now starts flowing into the snapshot. after sometime, I recieved an error message stating that there was an error deleting the data from the database because one the dependent snapshot did not had free space in it.

I thought, now I wont be able to delete any more data from the database, but opposite to my belief, the next time I executed the delete statement, it ran without any errors. Then I checked the state of my snapshot and it was in the "suspect mode". To handle this scenario, I created some more space in the drive to hold the data for snapshot, but even after creating the space the snapshot didn't came out of the suspect mode.

So the moral of the story is that, you can have a snapshot on a disk with smaller amount of space as compared to your database, but when the space is full, the snapshot recovery doesn't seems to be possible.

I hope, this article will clear a lot of concepts regarding database snapshots.