Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Differential Backup Size Expand / Collapse
Author
Message
Posted Tuesday, March 16, 2010 11:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 21,339, Visits: 15,008
double post



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #884421
Posted Wednesday, March 17, 2010 12:02 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 11, 2014 8:07 PM
Points: 891, Visits: 235
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.






Post #884423
Posted Wednesday, March 17, 2010 12:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 231, Visits: 737
Oke I will try your comments
Post #884437
Posted Wednesday, March 17, 2010 4:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 7,133, Visits: 6,295
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #884512
Posted Wednesday, March 17, 2010 8:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 11, 2014 8:07 PM
Points: 891, Visits: 235
Hi Tarvin,

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

Thanks again !!



Post #884674
Posted Wednesday, March 17, 2010 9:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 7,133, Visits: 6,295
Glad we could help.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #884777
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse