Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Database Snapshot and Rollback Transactions Expand / Collapse
Author
Message
Posted Wednesday, January 6, 2010 10:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 11:45 AM
Points: 169, Visits: 282
Comments posted to this topic are about the item Database Snapshot and Rollback Transactions


subban
Post #843306
Posted Thursday, January 7, 2010 4:08 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 8, 2010 5:40 PM
Points: 3, Visits: 12
Very interesting. Thanks for sharing!

_______________________
Giammarco Schisani
Volpet Software - Table Diff
Post #843416
Posted Thursday, January 7, 2010 6:27 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 4, 2011 7:02 AM
Points: 65, Visits: 265
Yes, very interesting...thank you. Always good to know whats "really" going on.



Dave Coats
Post #843471
Posted Thursday, January 7, 2010 8:20 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #843591
Posted Thursday, January 7, 2010 9:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
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
Post #843684
Posted Thursday, January 7, 2010 3:22 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
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
Post #843935
Posted Thursday, January 7, 2010 6:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:16 PM
Points: 31,018, Visits: 15,456
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
Post #844025
Posted Thursday, January 7, 2010 7:19 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 9,923, Visits: 11,169
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #844054
Posted Thursday, January 7, 2010 11:32 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
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
Post #844119
Posted Friday, January 8, 2010 12:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 11:45 AM
Points: 169, Visits: 282
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
Post #844134
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse