Database Snapshot and Rollback Transactions

Question

Post reply

Database Snapshot and Rollback Transactions

Subhash-63067

SSCarpal Tunnel

Points: 4171
More actions
January 6, 2010 at 10:22 pm

#214319

Comments posted to this topic are about the item Database Snapshot and Rollback Transactions

subban

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Giammarco Schisani SSC Journeyman Points: 77 More actions · Answer 1

Very interesting. Thanks for sharing!

_______________________
Giammarco Schisani
Volpet Software - Table Diff[/url]

Dave Coats SSC Eights! Points: 885 More actions · Answer 2

Yes, very interesting...thank you. Always good to know whats "really" going on.

Dave Coats

SanjayAttray SSChampion Points: 13157 More actions · Answer 3

For us it actually/nearly crashed a server. Original DB size was 34.45 GB but snapshot grew more than 78 GB where the Disk size allocated was 80 GB.

From there on my rule for creating a snapshot is that you should have at least 3 - 4 times the disk space compared to original database if the transaction level is high on original database. And that you if you have rollback statements.

SQL DBA.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 4

This is kind of obvious. A rollback of a transaction is a logical operation which generates more changes to the database - there's no concept of simply undoing a transaction's effects. If the undo operations affect more database pages that aren't already pushed into the snapshot, they must be pushed into the snapshot, thus increasing it's size.

However, your experiment doesn't show this at all. You only measure the increased size of the snapshot after the insert *and* rollback. If you measure the size after the insert, then do the rollback, and measure the size again, you'll see that in the example you do, the rollback doesn't cause much increase in the snapshot size at all compared to the size increase from the original transaction, as the undo operations of the rollback are on the same pages that the original transaction was on, and they've already been pushed into the snapshot. A few changes will occur in this case because it's a heap, but there should be very minimal growth for a clustered index.

I'll do a blog post today explaining how this really works, as this article is incorrect as it's written.

Thanks

(Edited after posting to be more clear)

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

Here's my blog post which explains how this works and how transaction rollbacks do NOT cause pages to be copied into the database snapshot.

http://www.sqlskills.com/BLOGS/PAUL/post/Misconception-around-database-snapshots-and-transaction-rollbacks.aspx

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 6

Thanks, Paul.

I added a note to your blog as well. If that's correct, I'll edit the article to make sure people understand. I think the article is a little ambiguous as to when the size increases.

Paul White SSC Guru Points: 150468 More actions · Answer 7

The way I read the article, the question was whether rolling back a transaction would also roll back any writes to the snapshot. This is quite different from suggesting that the act of rolling back a transaction results in any additional writes to the snapshot. I have re-read the article, and I still think it is reasonably clear on that point.

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

The problematic statement there is "no changes were made to the data page in the source database and hence no changes should be made to the snapshot database files". As Paul mentions, although the row data will be the same after a rollback, changes are made to the page header and so the page is physically different after the rolled back transaction, and so must remain in the snapshot.

There might be an expectation that rolling back a transaction would also roll back any writes to the snapshot. The article shows that this is not the case - a result which may not in fact be obvious to all.

It might also be surprising that all brand new allocations are also written to the snapshot - again, even if the transaction is rolled back. So, a structure which is created entirely within a transaction will grow the snapshot, even though it didn't exist at all before or after the transaction!

Snapshots are conceptually very simple, but there are many interesting features, which are not self-evident and nor are they documented. Bob Dorr of the CSS SQL Escalation Services team has a couple of interesting blog posts on the subject:

http://blogs.msdn.com/psssql/archive/2008/02/07/how-it-works-sql-server-2005-database-snapshots-replica.aspx

http://blogs.msdn.com/psssql/archive/2009/01/20/how-it-works-sql-server-sparse-files-dbcc-and-snapshot-databases-revisited.aspx

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul Randal One Orange Chip Points: 29544 More actions · Answer 8

I guess you could interpret the semantics of the article prose either way - at best it's ambiguous.

Snapshots are one of the features I liked the most when I helped write the code for them for SQL 2005, and also one of the most fun to teach as they're really non-intuitive in the way they behave.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Subhash-63067 SSCarpal Tunnel Points: 4171 More actions · Answer 9

Thanks Paul R and Paul W for giving a deep insight.

First of all the article points to the Rollback'd transaction as a whole, not the Rollback statements.

And the intention of the document is to convey the readers that a transaction which is rolled back can still affect the snapshot database even though there is no "logical" changes to the original database.

Yes it is an expected behaviour but most of us wouldn't have considered this.

subban

cjeremy74 Ten Centuries Points: 1135 More actions · Answer 10

This does not seem intuitive to me (that a snap shot can can actually be bigger than that source db). Am I missing the obvious?

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 11

This link might help you:

http://msdn.microsoft.com/en-us/library/ms175823.aspx

Gethyn Elliswww.gethynellis.com

cjeremy74 Ten Centuries Points: 1135 More actions · Answer 12

Thanks for the article - I took a look and it still seems odd to me that a snapshot could be 2-3x's the size of the source db. If the snapshot file is a copy of the original pages that were changed - how can the size ever be more than that of the original db? Seems to be the the worst case is that every page is in the snapshot file and therefore should be right at the size of the source db. I will do some add'l digging to see if I can answer this myself. Thanks again.

Jeremy.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 13

It's not - it can't get any bigger than the size of the database at the time the snapshot was created. The only time the snapshot can be bigger than the database is if the database is physically shrunk after the snapshot is created and WITH NOTRUNCATE is not specified.

Why do you think the article implies that the snapshot will be bigger than the database? It doesn't. The two byte sizes quoted are for the snapshot, not the database.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

cjeremy74 Ten Centuries Points: 1135 More actions · Answer 14

I was confused by this post...

SanjayAttray (1/7/2010)
For us it actually/nearly crashed a server. Original DB size was 34.45 GB but snapshot grew more than 78 GB where the Disk size allocated was 80 GB.
From there on my rule for creating a snapshot is that you should have at least 3 - 4 times the disk space compared to original database if the transaction level is high on original database. And that you if you have rollback statements.