Database Snapshots

Question

Database Snapshots

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
January 14, 2009 at 12:05 am

#75876

Comments posted to this topic are about the item Database Snapshots

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alberto dbLearner Ten Centuries Points: 1246 More actions · Answer 1

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??

Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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, MVP, M.Sc (Comp Sci)
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

Joseph-411461 Ten Centuries Points: 1228 More actions · Answer 3

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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, MVP, M.Sc (Comp Sci)
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

TomThomson SSC Guru Points: 104773 More actions · Answer 5

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