Database Snapshot and Rollback Transactions

Introduction

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
On
(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
go
Create Table Tab_SNPTest (A int)

Now insert couple of records in to this table

Declare @i int
Set @i=1
While(@i<100000)
 Begin
  Insert into Tab_SNPTest values(@i)
  set @i=@i+1
 End

Now create a snapshot for this database

Create Database DB_SRC_SNP
 on
 (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 
GO

Begin transaction 
Update Tab_SNPTest set a=16
Rollback

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)

Conclusion

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.