none
Hourly differential backups are too big RRS feed

  • Question

  • Hello,

    Please let me first apologize if this isn't the correct forum.

    I'm having a problem that, for the life of me, I can't seem to resolve.  To begin with, I'm using MSSQL Server 2012.

    I created a new MSSQL maintenance plan to perform a full nightly (12:00 am) backup for one of our databases. This new plan replaced an old plan that did nothing but perform a full backup. With this new plan I do the following:

    Check DB Integrity, Backup DB (full), Shrink DB, Rebuild Index, Clean Up History, Maintenance Cleanup Tasks.

    The size of our DB is only 12GB

    Now, the new plan took effect last Friday and it functioned properly as it made a full backup of the DB. The problem I'm having is that the differential backups I'm also doing every hour are now 10 times as big as they were before the new plan went into effect. Just to compare, before midnight Friday (when the new full backup plan took effect), my hourly differential backups were only in the range of 750MBs. My first differential backup after that came to 4+ GBS.  So, as you can see, the increase is somewhat alarming. Being that we have individual maintenance plans for both full and differential backups and being that I did not touch/modify our differential plan, can anyone please tell me why the size of these hourly differential backup plans are so big now? How is it that creating a new full backup plan affect the size of the differential files?  Could this have anything to do with me doing db shrinks and/or Index rebuilds?

    I'm at a loss here so I will really appreciate any feedback on this. Thank you very much!

    Rob

    P.S. Just in case, here's a screenshot of the differences:


    • Edited by galileo1210 Monday, October 13, 2014 3:04 PM
    Monday, October 13, 2014 2:30 PM

Answers

  • Check what time your main maintenance plan complete. 

    I think its because of the rebuild indexes/checkdb that it is causing the differential backups to take more time. Is there any other overnight jobs that run during the same time? For a day you could disable rebuild indexes/checkdb and see if you still see huge differential backup.

    Are you also shrinking the db every night? If so please dont do it unless you think it is crucial.  - http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by galileo1210 Tuesday, October 14, 2014 12:59 PM
    Monday, October 13, 2014 2:38 PM
    Answerer
  • Index rebuilds will create lots of log entries. Try doing the index maintenance before the full backup that way the incremental backups don't need to back those log entries up each time.

    • Marked as answer by galileo1210 Tuesday, October 14, 2014 12:59 PM
    Monday, October 13, 2014 2:41 PM
  • I would talk about something not related to your actual but would help you. You did not told about recovery model of database since you do not take transaction log backup I guess its simple. If its full and still you do not take that is a blunder. Please take that into account as well

    As already said

    Complete Index rebuild followed by complete shrink is what IMO causing differential backups to go high leaving to. you are actually putting lot of work on your system only rebuild indexes which are fragmented >30 % and rebuild if fragmentation is between 10 and 30. You can create your won script of use Ola Hallengren script.

    Create a different plan for integrity check dont include this is backup plan.

    PS: Absolutely no need for shrinking file or database operation for database with just 12 G size


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Monday, October 13, 2014 3:19 PM
    Moderator
  • In addition to the other posts: Not only do you perform rebuild after the full backup, but you also shrink. Both of these will modify lots and lost (close to all) pages in the database, resulting in your huge diff backups thereafter. I suggest you don't shrink at all and do the rebuild (possibly using a smarter script) before the full backup.

    Don't shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by galileo1210 Tuesday, October 14, 2014 12:58 PM
    Monday, October 13, 2014 8:18 PM
    Moderator

All replies

  • Check what time your main maintenance plan complete. 

    I think its because of the rebuild indexes/checkdb that it is causing the differential backups to take more time. Is there any other overnight jobs that run during the same time? For a day you could disable rebuild indexes/checkdb and see if you still see huge differential backup.

    Are you also shrinking the db every night? If so please dont do it unless you think it is crucial.  - http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by galileo1210 Tuesday, October 14, 2014 12:59 PM
    Monday, October 13, 2014 2:38 PM
    Answerer
  • Index rebuilds will create lots of log entries. Try doing the index maintenance before the full backup that way the incremental backups don't need to back those log entries up each time.

    • Marked as answer by galileo1210 Tuesday, October 14, 2014 12:59 PM
    Monday, October 13, 2014 2:41 PM
  • Thanks for your feedback, Ashwin.  The whole thing takes from between 8 and 11 minutes to complete.  There aren't any other jobs that run at the same time.  I, indeed, do both a shrink and the index rebuild every night.  Since I implemented the plan last Friday, I've removed the shrinking step from the process.  Unfortunately, the size is still at 4GB or so.  I'm going to remove (disable) the Index Rebuild step as well to see if this may be the culprit.

    And yes, I've been reading that shrinking the db on a regular basis isn't good.  I may just stop doing this nightly.

    Rob


    • Edited by galileo1210 Monday, October 13, 2014 3:09 PM
    Monday, October 13, 2014 2:53 PM
  • Hi James,

    You know, I didn't even think of the sequence of things when I set up the full plan.  I may just reorganize my tasks and see if that does solves the Diff size issue.

    Would you say rebuilding indexes every night is recommended though?  I know shrinking isn't but what about rebuilds?

    Thanks much!

    Rob

    Monday, October 13, 2014 2:57 PM
  • Hi Rob,

    Rebuilding all indexes daily is not necessary. I would actually recommend you to use the free solution by Ola Hallegran which only rebuilds indexes if an index is fragmented. I would not use or recommend to use maintenance plans for rebuild indexes/update stats as it lack many options and also Ola's solution is free and is used by 1000's of dba's across the globe.

    Take a look at this https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Monday, October 13, 2014 3:13 PM
    Answerer
  • I would talk about something not related to your actual but would help you. You did not told about recovery model of database since you do not take transaction log backup I guess its simple. If its full and still you do not take that is a blunder. Please take that into account as well

    As already said

    Complete Index rebuild followed by complete shrink is what IMO causing differential backups to go high leaving to. you are actually putting lot of work on your system only rebuild indexes which are fragmented >30 % and rebuild if fragmentation is between 10 and 30. You can create your won script of use Ola Hallengren script.

    Create a different plan for integrity check dont include this is backup plan.

    PS: Absolutely no need for shrinking file or database operation for database with just 12 G size


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Monday, October 13, 2014 3:19 PM
    Moderator
  • Ola Hallegran has built a great index maintenance script I would recommend scheduling this with SQL Agent to run each night.
    Monday, October 13, 2014 4:13 PM
  • Thanks very much, Ashwin, Shanky, and James.  I will definitely be taking a look (and possibly implementing) Ola's process for dealing with fragmented indexes.  It's nice to think that my plan itself maybe the one actually causing the issue and not something else.  As I mentioned, I've now disabled the options for shrinking and rebuilding indexes and will wait until the next full backup tonight (not allowed to execute during the day) to see if it has any effect on the size of the differential files.

    By the way, Shanky, my recovery model is FULL.  It was set up that way by whomever did this before I was asked to assume this role a month or so ago.

    Will report my results first thing tomorrow to see if things changed.

    I really appreciate all the help, guys!

    Rob


    • Edited by galileo1210 Monday, October 13, 2014 4:34 PM
    Monday, October 13, 2014 4:30 PM
  • Hi Rob,

    Rebuilding all indexes daily is not necessary. I would actually recommend you to use the free solution by Ola Hallegran which only rebuilds indexes if an index is fragmented. I would not use or recommend to use maintenance plans for rebuild indexes/update stats as it lack many options and also Ola's solution is free and is used by 1000's of dba's across the globe.

    Yeah...but not supported by Microsoft.  Use it at your own risk.

    It is better to use Maintenance Plan Wizard, a proper Microsoft product:

    Use the Maintenance Plan Wizard

    Generally, indexes should be rebuilt periodically like every weekend or every month end.

    STATISTICS should be updated nightly.

    Missing indexes should be eliminated.

    Optimization: http://www.sqlusa.com/articles/query-optimization/




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    Monday, October 13, 2014 8:02 PM
    Moderator
  • "It is better to use Maintenance Plan Wizard, a proper Microsoft product:"

    Please define "better" and "proper" in this context. Maint plans are pretty crappy and I think that most experienced DBAs agree on this. In the end, it is up to each individual/organization whether to use maint plans and have a GUI and something which ships with the product or use something which is way better but doesn't have a GUI and doesn't come with the product. But saying plainly that maint plans are better is IMO an incorrect statement.


    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, October 13, 2014 8:15 PM
    Moderator
  • In addition to the other posts: Not only do you perform rebuild after the full backup, but you also shrink. Both of these will modify lots and lost (close to all) pages in the database, resulting in your huge diff backups thereafter. I suggest you don't shrink at all and do the rebuild (possibly using a smarter script) before the full backup.

    Don't shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by galileo1210 Tuesday, October 14, 2014 12:58 PM
    Monday, October 13, 2014 8:18 PM
    Moderator
  • You're absolutely correct, Tibor.  To be honest, I'm just realizing how bad I sequenced this plan.  As stated above, I disabled the shrinking and rebuilding of indexes and I'm now waiting for the full backup to kick in.  I'm hopeful that the size of the files will be smaller afterward so I can then go back and re-order my task list.

    Quite frankly, my lack of Knowledge here is getting the best of this.  Lesson learned for the next time around for sure.

    I'll post my results after the backup.

    Thanks!

    Rob


    • Edited by galileo1210 Tuesday, October 14, 2014 1:40 AM
    Tuesday, October 14, 2014 1:39 AM
  • Hey all,

    Okay, I'm glad to report that what all of you suggested was right on the money!  After disabling both shrinking and indexing and then running a full backup, the differential file sizes dropped to what they were before I created the plan.  Just to check things out further, I re-enabled the rebuild index task and ran it before the full backup. This too proved to work great as the file sizes remain small.

    Thank you all very much for the help on this!!

    Rob

     

    Tuesday, October 14, 2014 12:58 PM