Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Differential Backup Size


Differential Backup Size

Author
Message
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21113 Visits: 18259
double post



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

vidya_pande
vidya_pande
SSC Eights!
SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)

Group: General Forum Members
Points: 891 Visits: 242
Hi GilaMonster,

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 got one more thread
http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/33a6e516-c7f7-4acd-a95c-7a1a84e41ff8

This also mentioning about index rebuilt and databas shrinking as one of the reason for large size of differential backups.



ESAT ERKEC
ESAT ERKEC
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 897
Oke I will try your comments
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7806 Visits: 8746
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/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.
vidya_pande
vidya_pande
SSC Eights!
SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)

Group: General Forum Members
Points: 891 Visits: 242
Hi Tarvin,

Thanks for the info.
Will get back if i come across any doubts on this.

Thanks again !!



Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7806 Visits: 8746
Glad we could help.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search