• vidya_pande (3/17/2010)


    Can you please give some references more information on how differential backup size increases because of Index Rebuilding process?

    I am not able to find any information on this.

    I'm not Gail, but here goes.

    Pick up a SQL Server reference book. Generally, it will have a section in the back called "Index". This index is grouped by subject matter. So it'll start with A, list topics starting with the letter 'A', some topics might have subtopics that are indented a little. For instance, in the book I just picked up, Aggregations is a topic with subtopics of Aggregation Design Wizard support (pp-pp), applying to data regions (pp), backing up (pp), defined (pp), etc.

    Each "pp" is a page number on which I can find the topic. This is called a pointer in SQL Server terms. Essentially, the Index is ordered alphabetically, which makes it a clustered index, and it points to data on pages outside of itself, which makes the pages indexed with a non-clustered index.

    Now say I have 500 new topics to insert into the book Index. I need to rearrange the text on these pages and insert blank pages to add these topics in. In doing this, I end up with a lot of extra page space between some of the letters. Say, 1/2 a blank page below the letter "C' which forces the "D" topics onto a brand new page. This is inelegant, hard to read, and just sloppy publishing. So I "rebuild" my indexes, squeezing the topics together to shove all the white space to the end of the book. But in order to do this, I have to copy all my Index pages on to brand new pages (cut-n-paste basically) because I can't just "shrink" a physical piece of paper without losing what's printed on the other side.

    So I copy the text to a brand new set of pages, in the process eliminating all the white space. But now I have double the number of pages I had to begin with. The only way to get rid of this extra space is to excise or truncate it. Free it up for my "operating system" so to speak.

    Now, in RL, SQL Server does actually "copy" the data it's reworking onto extra pages and extents (look these terms up in BOL) so it doesn't lose the data. Additionally, while it's working on rebuilding indexes, it's creating transactions that are, for the moment, completely uncommitted. It won't commit those transactions until it's finished rebuilding the index it's currently working on and even then, the transaction might remain uncommitted for several minutes / hours afterwards depending on your database load.

    Because SQL Server needs all that space to work in, it will expand the database to meet that need. The next backup you take will include all that extra space. And the database data file WILL NOT TRUNCATE unless you have Auto-Shrink on (bad idea) or you manually truncate every so often. The Transaction Log will not Truncate unless you manually truncate or you do Transaction Log backups. (Note: Shrinking and Truncating are two slightly different things.)

    And since a Differential backup takes every change since the last FULL backup (not the last Differential), it will continue to grow in size until you do another FULL backup. Whereas a Transaction Log backup might be huge the first time you do it after an Index rebuild, but it will truncate and shrink itself shortly thereafter, making the resulting Transaction Log backups smaller than the first one.

    Does that help?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.