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

Database Snapshot and Rollback Transactions

By SUBHASH KV,

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.

Total article views: 4649 | Views in the last 30 days: 6
 
Related Articles
FORUM

Database SnapShot

Database SnapShot

FORUM

Problems in creating database snapshots

Problems in creating database snapshots

FORUM

Creating a Database Snapshot.

Creating a Database Snapshot, without space in disk.

SCRIPT

Create Snapshot Dynamicly

Create Snapshot Dynamically considering multiple database files

BLOG

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...

Tags
 
Contribute

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
Editor, SQLServerCentral.com

Already a member? Jump in:

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