Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Snapshot and Rollback Transactions


Database Snapshot and Rollback Transactions

Author
Message
Subhash-63067
Subhash-63067
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 Visits: 295
Comments posted to this topic are about the item Database Snapshot and Rollback Transactions


subban
Giammarco Schisani
Giammarco Schisani
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 12
Very interesting. Thanks for sharing!

_______________________
Giammarco Schisani
Volpet Software - Table Diff
Dave Coats
Dave Coats
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 265
Yes, very interesting...thank you. Always good to know whats "really" going on.

Dave Coats
SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4021 Visits: 1619
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
Paul Randal
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2377 Visits: 1715
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
Paul Randal
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2377 Visits: 1715
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
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40157 Visits: 18845
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11060 Visits: 11351
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2377 Visits: 1715
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
Subhash-63067
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 Visits: 295
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search