Database Snapshot and Rollback Transactions

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


    subban

  • Very interesting. Thanks for sharing!

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

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

    Dave Coats

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

  • 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

  • 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

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

  • 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

  • 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

  • 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

  • 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?

  • This link might help you:

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

    Gethyn Elliswww.gethynellis.com

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

  • 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

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

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

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