Database Snapshots

  • Comments posted to this topic are about the item Database Snapshots

  • 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

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

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

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

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