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

Database Snapshots Expand / Collapse
Author
Message
Posted Wednesday, January 14, 2009 12:05 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
Comments posted to this topic are about the item Database Snapshots






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #636030
Posted Wednesday, January 14, 2009 6:24 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 12:34 PM
Points: 480, Visits: 214
As far as I know, when you create a Snapshot, it reserves disk space to match the database size. If the drive doesn't have that amount of disk space you can't create the snapshot. If it does, as I said, the disk space is reserved. So: how can a snapshot run out of disk space??
Post #636176
Posted Wednesday, January 14, 2009 8:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 40,208, Visits: 36,617
A snapshot is a sparse file. The size of the file appears to be the size of the database, but it hasn't reserved that much space on the disk. The size on disk is much, much smaller, the size only of the data inside.

See the section in Books Online "Understanding Sparse File Sizes in Database Snapshots"



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #636258
Posted Wednesday, January 14, 2009 10:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:48 AM
Points: 1,036, Visits: 141
When I drop a snapshot and recreate it, then it isn't the same snapshot any more, is it? What if I run some kind of audit that requires the snapshot to represent a specific date and time?
Post #636450
Posted Wednesday, January 14, 2009 1:05 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 40,208, Visits: 36,617
Joseph (1/14/2009)
What if I run some kind of audit that requires the snapshot to represent a specific date and time?


Don't let the disk run out of space. In addition, make sure you don't have to restore or detach the database ever. Make sure you'll never need to drop files.

My personal opinion, if you have to have a copy of the database as it was at a specific point in time, take a backup and either restore it and mark the new DB readonly, or archive the backup. Snapshots are not good for long-term usage or for cases where you absolutely have to have the snapshot. There are too many reasons why you may need to drop them.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #636611
Posted Saturday, October 2, 2010 8:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:02 PM
Points: 7,801, Visits: 9,553
Why should you expect dropping the snapshot and recreating it to work if you don't allocate some extra space for it? Surely it will just run out of space again?

Tom
Post #997204
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse