Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Database Snapshot and Rollback Transactions



SQL Server 2005 Database Snapshots provide a consistent read-only view of the source database as it existed at the time of the snapshot creation. Database snapshots work on the page-level by moving data from the source database to the snapshot. Before the source database page gets modified for the first time, the original page is copied to the snapshot database (COPY ON WRITE to a sparse file).

We all expect the snapshot database files will get updated when there is a change in source database page. However, what happens if a transaction gets rolled back? Effectively no changes were made to the data page in the source database and hence no changes should be made to the snapshot database files. Is that correct? The answer is NO.

Let's try this

Here we create a source database DB_SRC and then Create a table Tab_SNPTest

Create Database DB_SRC
(Name='DB_SRC', FileName='C:\Test\DB_SRC.MDF')
Log ON
(Name='DB_SRC_Log', FileName='C:\Test\DB_SRC_LOG.LDF') Use DB_SRC
Create Table Tab_SNPTest (A int)

Now insert couple of records in to this table

Declare @i int
Set @i=1
Insert into Tab_SNPTest values(@i)
set @i=@i+1

Now create a snapshot for this database

Create Database DB_SRC_SNP
(name='DB_SRC', FileName='C:\Test\DB_SRC_SNP.MDF')
AS SnapShot of DB_SRC

Now get the current sizes of snapshot sparse file using the command below.

select size_on_disk_bytes 
 from sys.dm_io_virtual_file_stats(DB_Id('DB_SRC_SNP'),1)

For me it was 196608 bytes. Now start a transaction which updates the records in Tab_SNPTest and then do a rollback.

use DB_SRC 
Begin transaction 
Update Tab_SNPTest set a=16

Check the size of the sparse file using the below command again, the new size of the snapshot database sparse file is increased (in my case 1835008 Bytes)

select size_on_disk_bytes 
 from sys.dm_io_virtual_file_stats(DB_Id('DB_SRC_SNP'),1)


Yes, the rollback transactions increase the size of the sparse file. Its clear that the snapshot logic does COPY ON WRITE for transactions that are rolled back as well. So be careful when you plan sparse file usage size for databases snapshots, it may increase unexpectedly even if there are no effective changes to the original database.

Editor's Note:

There appears to some confusion about this article and what it implies. When you begin a transaction that modifies data, the changed data pages will be written to the snapshot at that time, if they have not already been modified since the snapshot was created. Pages changed after snapshot creation are only written once to the snapshot, regardless of how many times they are changed in the source database.

If the transaction that modifies the data is rolled back, meaning no changes to the source database at the end of the rollback, the writes to the snapshot are not rolled back. They remain in the snapshot, and could have increased the size of the snapshot database.

Total article views: 4683 | Views in the last 30 days: 4
Related Articles

Database SnapShot

Database SnapShot


Problems in creating database snapshots

Problems in creating database snapshots


Creating a Database Snapshot.

Creating a Database Snapshot, without space in disk.


Create Snapshot Dynamicly

Create Snapshot Dynamically considering multiple database files


A Script A Day - Day 6 - Drop and Create Database Snapshots

Today's Script will drop all database snapshots and create a database snapshot for all online read w...


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones