SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Snapshot and Rollback Transactions


Database Snapshot and Rollback Transactions

Author
Message
cjeremy74
cjeremy74
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 346
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?
Gethyn Ellis
Gethyn Ellis
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1807 Visits: 2890
This link might help you:

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

Gethyn Ellisgethynellis.com
cjeremy74
cjeremy74
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 346
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
Paul Randal
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4791 Visits: 1717
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
cjeremy74
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 346
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.

Paul Randal
Paul Randal
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4791 Visits: 1717
They had to have something else going on, or they're confused by nomenclature - snapshot just can't get bigger than the database - once all pages from the database are copied, there's nothing else that can make the snapshot grow.

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
cjeremy74
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 346
Thanks for the reply and add'l detail. Glad to know that I was not missing something on the file size.

Jeremy.
Paul White
Paul White
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20752 Visits: 11359
Paul Randal (1/11/2010)
...snapshot just can't get bigger than the database - once all pages from the database are copied, there's nothing else that can make the snapshot grow.

True, but it could appear that way:

Consider a database 80GB in size.
A snapshot of that database reaches 78GB in size.
Objects are then deleted from the main database.
The main database is then shrunk to 1GB.
The snapshot is now larger than the database, since extents are never deallocated from the snapshot.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul Randal
Paul Randal
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4791 Visits: 1717
Yes, that's what I said two replies further up in the thread...

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 White
Paul White
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20752 Visits: 11359
Paul Randal (1/12/2010)
Yes, that's what I said two replies further up in the thread...

So you did Laugh
I read that post more than once and missed that part every time! Oh well. The point is well made then I guess.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
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