Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Incremental backup size increasing after deleting a bunch of records

    Hi, we have DB2 version 8.2 FP 11 and for the past few days I have been running a script that has been doing a significant number of deletes on a big table. I have been regularly pruning the log files before the backup occurs and today I noticed that the incremental backup size has gone up quite a bit consistently since I started.. is there some other kind of clean up I can take care of to ensure that the incremental doesn't increase as much? Here is the incremental backup command that runs every night --

    db2 backup db dbname online incremental to /path1/backup COMPRESS include logs


    Thanks!
    Last edited by db2user24; 05-02-13 at 19:15.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Incremental backups include all the data pages that had a change of any type. All of those deletes you have been doing are changing data pages--as it seems, more than you normally would have. This is why the backup images are larger.

    Andy

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    Thanks Andy.. do you know if this would have any effect on the next full backup image? We currently have a full backup once a month and the other days it's the incremental.
    Maybe the solution is for me to run a full backup every Sun and incremental from Mon - Sat.. this way the incrementals won't be as big. Any thoughts? Thanks!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ARWinner View Post
    Incremental backups include all the data pages that had a change of any type
    ...since the last full backup. In other words, as soon as you take a full backup, the subsequent incremental backups will begin from scratch, each growing in size compared to the preceding incremental.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Probably not. The full backup copies all the pages, regardless of whether they changed or not. SO the image will only increase if more data pages are added. Deleting will not add data pages, so there will be no increase to the entire backup image.

    Andy

  6. #6
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by ARWinner View Post
    Probably not. The full backup copies all the pages, regardless of whether they changed or not. SO the image will only increase if more data pages are added. Deleting will not add data pages, so there will be no increase to the entire backup image.

    Andy

    Great thanks! Any idea if the same holds true if i'm moving data from one table to another? Another thing we'd like to do in the future is archive data.. ie move from one table to an archive table.

    I think changing the full / incremental backup frequencies will solve my problem and keep the incrementals somewhat small.. thanks for the help guys!

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2user24 View Post
    if i'm moving data from one table to another?
    "moving data from one table to another" indeed means deleting records from one table and inserting them into another, so a single "moved" record will mark two pages as changed, which will cause both to be included in the incremental backup image.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •