Database Snapshot and Rollback Transactions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by SUBHASH KV » Database Snapshot and Rollback Transactions

22 posts, Page 1 of 3

1 2 3 »»»

Database Snapshot and Rollback Transactions

Rate Topic

Display Mode

Topic Options

Author

Message

Subhash-63067

Posted Wednesday, January 06, 2010 10:22 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:09 AM
Points: 169, Visits: 276

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

subban

Post #843306

Giammarco Schisani

Posted Thursday, January 07, 2010 4:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 08, 2010 5:40 PM
Points: 3, Visits: 12

Very interesting. Thanks for sharing!

_______________________
Giammarco Schisani
Volpet Software - Table Diff

Post #843416

Dave Coats

Posted Thursday, January 07, 2010 6:27 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 04, 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

SanjayAttray

Posted Thursday, January 07, 2010 8:20 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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

Paul Randal

Posted Thursday, January 07, 2010 9:55 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601

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

Paul Randal

Posted Thursday, January 07, 2010 3:22 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601

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

Steve Jones - SSC Editor

Posted Thursday, January 07, 2010 6:15 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 6:14 PM
Points: 31,421, Visits: 13,734

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

Paul White

Posted Thursday, January 07, 2010 7:19 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 7:18 PM
Points: 10,989, Visits: 10,532

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

Paul Randal

Posted Thursday, January 07, 2010 11:32 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601

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

Subhash-63067

Posted Friday, January 08, 2010 12:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:09 AM
Points: 169, Visits: 276

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 »

22 posts, Page 1 of 3

1 2 3 »»»

Permissions