none
Understanding replica volume and recovery point volume usage with SQL Express Full Backup RRS feed

  • Question

  • I am running some trials to test DPM 2012 R2's suitability for protection a set of SQL Server databases and I am trying to understand what happens when I create a recovery point with Express Full Backup.

    The databases use simple recovery model and in the tests I have made so far I have loaded more data into the databases between recovery points since that will be a typical scenario - the databases will grow over time. The database files are set to autogrowth by 10%

    I have been looking at the change in USED space in the replica volume and in the recovery point volume after new recovery points and have a hard time understanding it.

    After the first test where data was loaded into the database and an Express Full Backup recovery point was created, I saw an increase in used space in the replica volume of 85 Gb and 29 GB in the recovery point volume. That is somewhat more than I think the database grew (I realize that should have monitored that, but did not), but anyway it is not completely far out.

    In the next test I did the same thing except I loaded twice as much data into the database.

    Here is where it gets odd: This causes zero increased usage in the replica volume and 33 GB increased use in the recovery point volume.

    I do not understand why the replica volume use increases with some recovery points and not with others.

    Note that I am only discussing increased usage in the volumes - not actual volume growth. The volumes are still their original size.

    I have been using 3-4 days on the test and the retention period is set to 12 days, so nothing should be expired yet.

    Monday, July 7, 2014 1:02 PM

Answers

  • Hi,

    The replica volume usage represents the physical database file(s) size. The database file size on the replica should be equal to the database file size on the protected server.  This is both .mdf and .ldf files.  If when you load data into the database and you overwrite current tables versus adding new ones, or if there is white space in the database files and the load simply uses that white space, then there will not be any increase in the file size, so there will not be any increase in the replica used space.

    The recovery point volume will only contain delta changes applied to the database files.  As the changed blocks overwrite the files on the replica during express full backup, VSS (volsnap.sys) driver copies the old blocks about to be overwritten to the recovery point volume before allowing the change to be applied to the file on the replica. 

    Hope this helps explain what you are seeing.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, July 9, 2014 3:55 AM
    Moderator

All replies

  • Hi,

    The replica volume usage represents the physical database file(s) size. The database file size on the replica should be equal to the database file size on the protected server.  This is both .mdf and .ldf files.  If when you load data into the database and you overwrite current tables versus adding new ones, or if there is white space in the database files and the load simply uses that white space, then there will not be any increase in the file size, so there will not be any increase in the replica used space.

    The recovery point volume will only contain delta changes applied to the database files.  As the changed blocks overwrite the files on the replica during express full backup, VSS (volsnap.sys) driver copies the old blocks about to be overwritten to the recovery point volume before allowing the change to be applied to the file on the replica. 

    Hope this helps explain what you are seeing.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, July 9, 2014 3:55 AM
    Moderator
  • Thanks Mike. That does explain it.

    As it turns out the database file encountered an autogrowth event during the first test and expanded the database file by 10 %, which was room enough for all the data loaded in the second test. So that explains why the replica volume did not grow in my second test.

    Wednesday, July 9, 2014 9:41 AM