Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    3

    Unanswered: differential backup shrinks?

    I am running a differential backup on a SQL 2000 system. As expected after performing a backup, the subsequent differential b/ups are small and generally grow from there. However, during some parts of the day, the differential file will grow dramatically and then get smaller. HUH? Based on all that I have read and know about diff b/ups, I don't see how the differential b/up can shrink without doing a full backup.
    Any ideas what would cause this to occur?
    TIA,
    Ron

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Post Re: differential backup shrinks?


    I am running a differential backup on a SQL 2000 system. As expected after performing a backup, the subsequent differential b/ups are small and generally grow from there. However, during some parts of the day, the differential file will grow dramatically and then get smaller. HUH? Based on all that I have read and know about diff b/ups, I don't see how the differential b/up can shrink without doing a full backup.
    Any ideas what would cause this to occur?
    TIA,
    Ron
    Q1 I don't see how the differential b/up can shrink without doing a full backup. Any ideas what would cause this to occur?
    A1 There are scenarios in which this may occur; the most common involves special purpose [transient] index manipulations, others sometimes involve large daily updates.

    Index Manipulation Example:
    Adding a new [temporary] index for a short term ad hoc purpose or making relativly few transient index changes on a large index can make for a lot of changes relative to the last full dump. Subsequently dropping an added index and / or undoing changes to an existing index that has been manipulated may then easily make the size of a later Diff dump smaller than the size of a previous Diff dump. Essentially, the [later] state of the DB in such cases is much closer to the original state of the DB (before transient index manipulations occurred).

    Large Daily Update Example:
    Consider active / inactive status column changes that are mostly inactive before the business day, active durring it, and inactive following the business day, and on weekends).

  3. #3
    Join Date
    Nov 2002
    Posts
    3
    Great point. Some other DBA's and I thought of that last night and will check for that on Monday.
    Much Thanks,
    Ron

  4. #4
    Join Date
    Oct 2002
    Posts
    369
    Originally posted by Ronner
    Great point. Some other DBA's and I thought of that last night and will check for that on Monday.
    Much Thanks,
    Ron
    You are welcome; just out of curiosity, was there a fairly clear cause?

  5. #5
    Join Date
    Nov 2002
    Posts
    3
    Haven't identified it yet.
    Hasn't occurred since Thurs evening.
    I will post when it recurrs.
    There are some managment reports and functions on this system that might generate some new objects that are subsequently deleted.
    At least that's the theory.
    I'll let you know.
    Ron

Posting Permissions

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