Post reply

Differential Sizes Increase After Index Rebuild

  • May 13, 2013 at 10:07 am

    #277720

    Hi Folks

    I am trying to understand why my differential backups are almost as big as my full backups

    after rebuilding the indexes

    My full backups are at 12:30 am every night with differentials every 4 hours

    On sat at 2am I rebuild the indexes

    I then notice that my differentials until the next full are huge, almost as big as the full

    I am under the impression that I should rebuild indexes after a full but how do I get my differential sizes down after that rebuild ?

    do i then need to do another full ?

    Thanks

    Jim

  • May 13, 2013 at 10:11 am

    #1614679

    Hi Jim,

    A differential backup contains all the changes made since the last full backup.

    In your case, if you are taking a full backup and then rebuilding the indexes afterwards, all the changes involved in rebuilding the indexes will be reflected in differential backups going forward. If you're rebuilding all indexes on all tables, then I would expect for your differential backups to be about the same size as your full backups.

    To prevent my differential backups from being excessively large, I tend to rebuild necessary indexes immediately before a full backup.

    Does this help?

    Bob

    _______________________________________________________________________________________________
    Bob Pusateri
    Microsoft Certified Master: SQL Server 2008

    Blog: The Outer Join[/url]
    Twitter: @SQLBob

  • May 13, 2013 at 10:30 am

    #1614689

    HI Bob

    thanks for your input.

    here is a followup then

    should i be rebuilding my indexes before the full backup then ?

    i thought i had read somewhere to do a full backup first

    secondly, i can understand why the first differential after the full is big, but why would the succeeding differentials also bebig, since there were probably no changes at that time of night ?

    Thanks

    jim

  • May 13, 2013 at 10:32 am

    #1614690

    JC-3113 (5/13/2013)

    HI Bob

    thanks for your input.

    here is a followup then

    should i be rebuilding my indexes before the full backup then ?

    i thought i had read somewhere to do a full backup first

    secondly, i can understand why the first differential after the full is big, but why would the succeeding differentials also bebig, since there were probably no changes at that time of night ?

    Thanks

    jim

    Because each differential backup has ALL changes to the database since the LAST full backup.

  • May 13, 2013 at 10:40 am

    #1614694

    Hi Lynn

    so I was under the wrong impression that a differential is not from one to the other but from the last full. so if I have a full at 12 am and diffs at 4am and 8am and 12pm, lets say

    then the only files i would need for a complete restore is the full and the 12pm file ?

    the 4am and 8 am files are of no value unless i wanted to restore after those times then ?

    Thanks

    Jim

  • May 14, 2013 at 12:49 am

    #1614848

    Hello,

    You need recent differential backup only .

    The size is disadvantage of defferential backup and if you want to over come that go with a log backup.

    Regards
    Durai Nagarajan

  • May 14, 2013 at 2:23 am

    #1614856

    It is worth looking at Ola Hallengren's index maintenance routines at http://ola.hallengren.com/.

    This checks statistics so that only those indexes that need rebuilding get processed. This can cut down the maintenance window needed for index rebuild.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • May 14, 2013 at 6:37 am

    #1614959

    Just remember that differential backups do not allow for point in time recovery, they only allow you to recover to that specific point in time that they were taken. If you need actual point in time recovery you need to be taking transaction log backups as well between the full backups and the differential backups.

  • May 14, 2013 at 6:40 am

    #1614961

    EdVassie (5/14/2013)

    It is worth looking at Ola Hallengren's index maintenance routines at http://ola.hallengren.com/.

    This checks statistics so that only those indexes that need rebuilding get processed. This can cut down the maintenance window needed for index rebuild.

    Ola's are very good. I have rolled my own at several previous employers using information from BOL and those worked well for me.

  • May 14, 2013 at 8:43 am

    #1615037

    Thanks Ed

    I have looked at Ola Hallengren's index maintenance routines

    I have not had time to convert my Maint Plans using his scripts

    Jim

  • May 14, 2013 at 8:44 am

    #1615038

    Hi Durai

    thanks for the input

    Jim

  • May 14, 2013 at 8:45 am

    #1615039

    Understood Lynn

    Thanks

    Jim

  • May 14, 2013 at 8:47 am

    #1615040

    Thanks Lynn

    can you tell me when it is best to update indexes ?

    before a full backup or after ?

    or do I need to do one before and after

    Thanbks

    Jim

  • May 14, 2013 at 12:14 pm

    #1615134

    JC-3113 (5/14/2013)

    Thanks Lynn

    can you tell me when it is best to update indexes ?

    before a full backup or after ?

    or do I need to do one before and after

    Thanbks

    Jim

    You will probably get several different answers - all of which are valid...

    I prefer rebuilding indexes prior to performing a backup. But I make sure that if that process fails it does not prevent the backup from being performed.

    Others will prefer to follow the backup and rebuild indexes...

    And yet, others prefer separating these out as completely separate operations. That is, a job to rebuild indexes could be scheduled to run at 1am and the backups scheduled to run at the same time - or completely different times with no dependencies.

    I use this last option when I can no longer meet the maintenance window.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • May 14, 2013 at 2:16 pm

    #1615194

    Hi Jeffrey

    thanks for the input

    that takes me back to my original issue

    I am rebuilding indexes on sat night after my full back up

    what happens then is that my differentials are huge because of the rebuild

    (every 4 hours)

    so i was going to rebuild the indexes before the full to alleviate the size of the differentials that follow..

    Jim

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply