February 8, 2008 at 6:00 am
I'm exploring the use of database snapshots.
We have some batch processing and the developers need to know what has changed after some of the loads run. They wanted to put triggers on all of the load tables but I'm worried about the performance impact so we're exploring snapshots. The idea is that they would take a snapshot, load data, use queries to compre the snapshot to the load db to determine which records had changed.
I know there are other ways to do this but we don't have time or scope to change the actual load process so loading a set of keys isn't an option. (just in case you were thinking that)
Snapshotting seems to be producing the desired results however for some reason when I create a snapshot the snapshot data file is the same size as the production database which is not small (80 GB) they would need to create several snapshots through out their daily process and want to keep each one around for a few days.
My understanding of what I've read about snapshots is that it wouldn't go and re-store all of the data, pages that don't change would be read from the main database. Has anyone else observed this behaviour? Are there any alternatives?
Questions, comments, feedback welcome!
C.
Chris.
February 8, 2008 at 8:45 am
Long story short, the space is more or less reserved. Look at disk management before and after you take a snapshot. No space was actually used up for your snapshot. The snapshot files won't actually consume space until your source database sees change, and it will only consume the source databases allocation prior to the change that took place.
edited for my awesome spelling
February 8, 2008 at 9:37 am
So by design it reserves the same amount of space in its data file as the (for lack of a better word) parent database?
When I look at the files through Explorer they are the same size. Is there any way to change this behaviour? The problem is that they are 80 GB a pop, so if they make 5 per day and retain 2 days that could be significant. (Used or not)
C.
Chris.
February 8, 2008 at 9:45 am
CDP (2/8/2008)
So by design it reserves the same amount of space in its data file as the (for lack of a better word) parent database?
Correct
When I look at the files through Explorer they are the same size. Is there any way to change this behaviour?
I do not know ... I would like to know myself if that is possible as it is very deceiving.
Something to keep in mind though, not sure if it helps, but you are able to put a snapshot file on a different drive ...
February 11, 2008 at 6:46 am
I took one for the team and opened an MS case for this. I spoke with an engineer on the snapshot team.
According to him, there is no trace flag and no way to change the behavior or shrink a snapshot file.
C.
Chris.
February 11, 2008 at 8:25 am
Good to know.
Also, just FYI, the name of the type of file (I think) is a sparse file. That it creates a file at a specified size, but doesn't actually consume it.
February 11, 2008 at 8:28 am
That was what the doc said but not what I'm seeing. When I create a file report on the sparse file/snapshot file on the disk, there is a fair bit of consumption of data and indexes.
Chris.
Chris.
February 11, 2008 at 8:30 am
Immediately after you create the snapshot or after you start altering the source database?
February 11, 2008 at 8:47 am
I know that it is not immediately after but I'm not sure when it gets larger.
I'm going to try creating a snapshot before I go home tonight and make sure people don't modify the data and then review the structure tomorrow morning.
Chris.
Chris.
February 12, 2008 at 8:13 am
I don't quite understand why the snapshot has to reserve the space but it doesn't use all that it reserves. On the snapshot file click properties and look at the size vs the size on disk. We have a snapshot whose "size" is 319 GB but the "size on disk" is only 166 MB. I imagine it reserves the amount of space so that if you keep it a long time, it will eventually fill up--this is just a guess though. If you find out the reason, please be sure to post it.
February 12, 2008 at 8:16 am
Microsoft stated that this was so that they would have the best performance if every page in the database changed.
C.
Chris.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply