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 Monday, January 11, 2010 10:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 2:04 AM
Points: 263, Visits: 324
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?
Post #845545
Posted Monday, January 11, 2010 10:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:04 AM
Points: 1,030, Visits: 2,795
This link might help you:

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


Gethyn Ellis

gethynellis.com
Post #845550
Posted Monday, January 11, 2010 12:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 2:04 AM
Points: 263, Visits: 324
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.
Post #845646
Posted Monday, January 11, 2010 1:35 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 2:04 AM
Points: 263, Visits: 324
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.
Post #845713
Posted Monday, January 11, 2010 2:31 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 2:04 AM
Points: 263, Visits: 324
Thanks for the reply and add'l detail. Glad to know that I was not missing something on the file size.

Jeremy.
Post #845726
Posted Monday, January 11, 2010 9:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:59 AM
Points: 11,194, Visits: 11,166
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #845846
Posted Tuesday, January 12, 2010 9:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:59 AM
Points: 11,194, Visits: 11,166
Paul Randal (1/12/2010)
Yes, that's what I said two replies further up in the thread...

So you did
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #846334
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse