Post reply

Database size growth after Rebuild/reorganize job.

  • January 22, 2015 at 5:34 am

    #314722

    Hi,

    I have a VLDB and have setup 'Rebuild' and 'Reorganize' index job every week. after completion of the 'Rebuild'/'Reorganize' job log generates 100+ GB. And after that another job 'Differential backup' takes place.

    The diff. backup gets huge and remains the same size. Is there anyway I can keep Diff. backup file size small ? I am not talking about compress backup though.

    Thanks

  • January 22, 2015 at 5:42 am

    #1772166

    Your diff backup will contain all the changes made since the last full backup. So it's recording all the maintenance changes that have been made whilst performing your index rebuilds.

    The only way to get the diff backup smaller is to take a full backup.

  • January 22, 2015 at 5:48 am

    #1772171

    Thanks for the quick reply. I am missing something here:

    I think Rebuilding index is to remove fragmentation, when you remove fragmentation you will have less or no page split, that mean you will have less page 8k pages. Then why Diff. backup grows!!! I am not taking about log file size, because log will be smaller when you take ANOTHER log backup, but different backup remains same!!!

  • January 22, 2015 at 5:53 am

    #1772173

    Have a look at this post:-

    http://www.sqlservercentral.com/Forums/Topic883566-357-1.aspx

    The first reply by GilaMonster should explain

  • January 22, 2015 at 7:28 am

    #1772215

    Diff backup includes all pages modified since last full backup. Was the page touched by reorganize? Then it must be included in the diff.

    If it gets too big, take a FULL instead. There's a tipping point where diff backup becomes pointless (it requires FULL + DIFF) and a FULL becomes more convenient. I think you hit that point.

    -- Gianluca Sartori

  • January 22, 2015 at 9:17 am

    #1772280

    You don't reorg/rebuild every index every week do you? You do have some type of check so that you rebuild only tables that are severely fragmented?

    If you're already doing that, then you need to review the indexes and consider changing them as needed. This may involve changing the fillfactor and/or key columns. You'd need to review at least the missing index and index usage stats to determine the best way to do that.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 22, 2015 at 9:34 am

    #1772293

    There are a few good solutions you might try:

    SQL Fool Index Defrag - http://sqlfool.com/2011/06/index-defrag-script-v4-1/

    Minion Reindex (haven't tried, Grant's review comes out next week) - http://www.midnightdba.com/Jen/2014/10/minion-reindex-is-here/

  • January 22, 2015 at 10:53 am

    #1772334

    Tac11 (1/22/2015)

    Hi,

    I have a VLDB and have setup 'Rebuild' and 'Reorganize' index job every week. after completion of the 'Rebuild'/'Reorganize' job log generates 100+ GB. And after that another job 'Differential backup' takes place.

    The diff. backup gets huge and remains the same size. Is there anyway I can keep Diff. backup file size small ? I am not talking about compress backup though.

    Thanks

    How big is that "VLDB" in GB?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 22, 2015 at 11:31 am

    #1772347

    Thanks you guys for your input.

    @jeff it's 1.5 TB

Viewing 9 posts - 1 through 9 (of 9 total)

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