<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Backups
»
Differential Backup Size
16 posts, Page 2 of 2
««
1
2
Differential Backup Size
Rate Topic
Display Mode
Topic Options
Author
Message
SQLRNNR
SQLRNNR
Posted Tuesday, March 16, 2010 11:58 PM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754,
Visits: 12,337
double post
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
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
vidya_pande
vidya_pande
Posted Wednesday, March 17, 2010 12:02 AM
SSC Eights!
Group: General Forum Members
Last Login: Monday, June 04, 2012 5:00 PM
Points: 891,
Visits: 225
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
ESAT ERKEC
ESAT ERKEC
Posted Wednesday, March 17, 2010 12:58 AM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 222,
Visits: 708
Oke I will try your comments
Post #884437
Brandie Tarvin
Brandie Tarvin
Posted Wednesday, March 17, 2010 4:21 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 6,668,
Visits: 5,695
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, MCDBA, MCSA
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
vidya_pande
vidya_pande
Posted Wednesday, March 17, 2010 8:00 AM
SSC Eights!
Group: General Forum Members
Last Login: Monday, June 04, 2012 5:00 PM
Points: 891,
Visits: 225
Hi Tarvin,
Thanks for the info.
Will get back if i come across any doubts on this.
Thanks again !!
Post #884674
Brandie Tarvin
Brandie Tarvin
Posted Wednesday, March 17, 2010 9:09 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 6,668,
Visits: 5,695
Glad we could help.
Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA
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 »
16 posts, Page 2 of 2
««
1
2
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
