Database Snapshots in SQL Server 2005

  • Bimal,

    Thanks for the clarification.  I went so far as to examine the properties of the drive and found that after dropping the snapshot, that available space went up on the drive by exactly the amount shown in the "size on disk" property of the snapshot file.

    This was very helpful.  I had abandoned use of the snapshot months ago when it looked like it was consuming the same space as the original.   We had noticed the performance hit in our testing and together with the perceived hit on disk space gave up. 

    Dang...  I hate it when I am wrong.






  • I am using database snapshots. It is one of the first features we made use of. I automate a job process to allow (certain trusted) users (processes) to request a snapshot. They would add a entry to a table in a database that they had access to - snapshot name, retention time (not to exceed 2 hours) , status. The job would run every 15 minutes checking the table. When a request came through a snapsot was created with the named request and the job then set the status to 'available'. They had a remote job check the table for a status change, do its queries, and then set the status to 'delete'. My job had a second step that looked for any 'delete' status and would delete those snapshots. If their process happen to fail before it could mark the snapshot status as 'delete' then the snapshot was deleted after exceeding the retention time. We used stored procedures to add entries to and make changes to the request table.

  • Hi,

    Just wonder, is it possible to have snapshot on certain tables in a database instead of entire database?


  • Christopher (11/26/2007)


    Just wonder, is it possible to have snapshot on certain tables in a database instead of entire database?


    Nope. A snapshot is created on database level. Could be a nice feature but I think this is done to preserve referential integrity. Otherwise it could get messy. 😉

  • To alleviate some of the snapshot reads occurring on the source database, you can use mirroring to extend the architecture out.

    ProdDB ----> MirrorDB ----> Snapshot.

    By taking snapshots of the mirror database, you can help alleviate the load caused by DSS types of operations. I've done some proof of concept work with this setup and as long as the mirror db is on fairly fast spindles, it's difficult (but not impossible) to cause the production database a performance impact by large read operations on the snapshot (which in turn are reading from the source db).

    This is one way I've found to make snapshots useful in production operations and not cause a performance impact when the snapshot read has to go back to the source database for the data - as most DSS operations against an OLTP system are likely to do.

    Your friendly High-Tech Janitor... 🙂

  • Do we have any possibilities to make snapshot just in particular objects not snapshot all DB!???

    Example is if I want to snapshot 3-4 tables every 15 min, then these results goes to another table like Master table as collector of the data from these 3-4 tables!

    Simple the snapshot every 15 min will send the data in Master table even if the these 3-4 tables has been updated or not!?


  • First of all, let me say that this is the most clear article about SS I've ever seen. Good job!!!.

    But I still have a question: If I create a SS at 23:00 everyday, that means that every change in de source database will be stored in every SS? or when I create a new SS on the same database source, the existing SS stops registering changes?

    Thank you very much


Viewing 7 posts - 16 through 21 (of 21 total)

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