Saturday, July 28, 2012

Database Snapshot, is it only for Data Modifications?

Database Snapshot consists of a read-only static view of the database without including the uncommitted transactions.

If I put all of the above stuff in one picture, it will be like this.

image

In simple terms, when we create a database snapshot, empty spare file will be created As and when data is changed in the database, data pages are moved to the sparse file. So if you are reading from the snapshot, modified data pages will be read from the sparse file while non-modified file pages are read from the source database.

Now the question is, Is it only the data modifications are sent to the sparse file.

Let us examine this.
 
Let me create a database and some sample data for it.
 
image

Then I will take a full back and update few data as shown below.

image

Now then, back to the business end.

Let us create a database snapshot.

image

After creating this let us see the size of the snap shot file size.

image

So the file size of the snapshot file is 3.06 MB but size on the disk is 128KB which is actual data size.

Now let me take another backup.

image

Let us see the file size again.

image

What! how come, I didn’t do any data changes, still the size on disk has increased more than twice without any data modification.

Explanation

When there are data changes after a full backup, it will set a bit value in page so that for differential backup it can identify which pages need to taken into the differential backup. This value will be reset with a full backup. Since you have done a full backup after the database snapshot creation, full backup will modify the data page. So database snapshot is not only for the data modification but anything changes to the data page will move the page to the database snapshot.

1 comment:

  1. According to me there have nice information

    about Data service .Data service is

    business increment process service. We get

    and make business growth in market and also

    get easy and good with best price and also

    with customer requirement as. So Today I get

    good Data service in UK .

    Thanks & Regards
    Data Supply Services UK

    ReplyDelete