Post reply

Snapshot Question

  • December 15, 2009 at 11:39 am

    #137676

    I have a 30 Gig DB.

    If I create a new snapshot of that DB, how much space will it need?

    I don't have much space to work with and this is a production DB. I don't want to risk running out of room during the creation of the snapshot.

    Will there be aay impact to users during the creation?

  • December 15, 2009 at 11:59 am

    #1092679

    When initially created, it will be empty. It will start occupying space as your database is updated as it needs to retain a copy of the data as it existed at the time the snapshot was taken.

  • December 15, 2009 at 12:10 pm

    #1092689

    Thanks Lynn.

    I was planning on creating a new snapshot at the end of every day. So I would have historical data. My users want the ability to search the snapshots to find balances on a given day.

    So, if I make a new snap every day, how would this effect space (size of the snapshots)?

  • December 15, 2009 at 12:27 pm

    #1092692

    It depends.

    Also, remember that, if for any reason, you have to restore the database you will have to drop all your snapshots.

  • December 15, 2009 at 12:30 pm

    #1092693

    Also, Jeff asked a question in the other thread that you still should answer.

    What you may need to do is write quesries that recalc the balances as of a certain date.

  • December 15, 2009 at 12:35 pm

    #1092696

    thanks yep

  • December 15, 2009 at 1:16 pm

    #1092719

    Do note that snapshots cannot be backed up. If one runs out of space it immediately becomes suspect and has to be dropped. Also, they're not free, there's an IO impact in having them, one that can slow down the main database. http://www.sqlservercentral.com/articles/Performance+Tuning/64080/

    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

  • December 15, 2009 at 1:17 pm

    #1092720

    If you are limited on space, I would be very careful with creating multiple database snapshots. Remember, each snapshot stores the changes in the database since the snapshot was created.

    So, if you have 7 days worth of snapshots - the snapshot from 7 days ago will be as large as needed to hold 7 days worth of changes. Then, the snapshot from 6 days ago - all changes since then, etc...

    Depending upon the frequency of changes and how often the data is changed - you could easily exceed the size limitations. Not to mention the fact that each snapshot is going to incur overhead on the server to maintain - which could have an impact on the performance of your system.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 8 posts - 1 through 8 (of 8 total)

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