SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Differential Sizes Increase After Index Rebuild


Differential Sizes Increase After Index Rebuild

Author
Message
JC-3113
JC-3113
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 620
Hi Folks

I am trying to understand why my differential backups are almost as big as my full backups
after rebuilding the indexes

My full backups are at 12:30 am every night with differentials every 4 hours
On sat at 2am I rebuild the indexes
I then notice that my differentials until the next full are huge, almost as big as the full

I am under the impression that I should rebuild indexes after a full but how do I get my differential sizes down after that rebuild ?

do i then need to do another full ?

Thanks
Jim
SQL_Bob
SQL_Bob
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 408
Hi Jim,

A differential backup contains all the changes made since the last full backup.

In your case, if you are taking a full backup and then rebuilding the indexes afterwards, all the changes involved in rebuilding the indexes will be reflected in differential backups going forward. If you're rebuilding all indexes on all tables, then I would expect for your differential backups to be about the same size as your full backups.

To prevent my differential backups from being excessively large, I tend to rebuild necessary indexes immediately before a full backup.

Does this help?

Bob

_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008

Blog: The Outer Join
Twitter: @SQLBob
JC-3113
JC-3113
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 620
HI Bob


thanks for your input.
here is a followup then

should i be rebuilding my indexes before the full backup then ?
i thought i had read somewhere to do a full backup first

secondly, i can understand why the first differential after the full is big, but why would the succeeding differentials also bebig, since there were probably no changes at that time of night ?

Thanks
jim
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39097 Visits: 38518
JC-3113 (5/13/2013)
HI Bob


thanks for your input.
here is a followup then

should i be rebuilding my indexes before the full backup then ?
i thought i had read somewhere to do a full backup first

secondly, i can understand why the first differential after the full is big, but why would the succeeding differentials also bebig, since there were probably no changes at that time of night ?

Thanks
jim


Because each differential backup has ALL changes to the database since the LAST full backup.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
JC-3113
JC-3113
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 620
Hi Lynn

so I was under the wrong impression that a differential is not from one to the other but from the last full. so if I have a full at 12 am and diffs at 4am and 8am and 12pm, lets say

then the only files i would need for a complete restore is the full and the 12pm file ?
the 4am and 8 am files are of no value unless i wanted to restore after those times then ?

Thanks
Jim
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1607 Visits: 2775
Hello,

You need recent differential backup only .

The size is disadvantage of defferential backup and if you want to over come that go with a log backup.

Regards
Durai Nagarajan
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5761 Visits: 3863
It is worth looking at Ola Hallengren's index maintenance routines at http://ola.hallengren.com/.

This checks statistics so that only those indexes that need rebuilding get processed. This can cut down the maintenance window needed for index rebuild.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39097 Visits: 38518
Just remember that differential backups do not allow for point in time recovery, they only allow you to recover to that specific point in time that they were taken. If you need actual point in time recovery you need to be taking transaction log backups as well between the full backups and the differential backups.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39097 Visits: 38518
EdVassie (5/14/2013)
It is worth looking at Ola Hallengren's index maintenance routines at http://ola.hallengren.com/.

This checks statistics so that only those indexes that need rebuilding get processed. This can cut down the maintenance window needed for index rebuild.


Ola's are very good. I have rolled my own at several previous employers using information from BOL and those worked well for me.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
JC-3113
JC-3113
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 620
Thanks Ed

I have looked at Ola Hallengren's index maintenance routines
I have not had time to convert my Maint Plans using his scripts

Jim
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