Database Snapshots

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 Steve Jones » Database Snapshots

Database Snapshots

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Wednesday, January 14, 2009 12:05 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:26 PM
Points: 31,425, Visits: 13,738

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

Alberto dbLearner

Posted Wednesday, January 14, 2009 6:24 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 6:22 PM
Points: 480, Visits: 210

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

GilaMonster

Posted Wednesday, January 14, 2009 8:07 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

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

Joseph-411461

Posted Wednesday, January 14, 2009 10:35 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 956, Visits: 131

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

GilaMonster

Posted Wednesday, January 14, 2009 1:05 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

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

L' Eomot Inversé

Posted Saturday, October 02, 2010 8:05 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 7,096, Visits: 7,156

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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #997204

« Prev Topic | Next Topic »

Permissions