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 Sizes Increase After Index Rebuild Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 10:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, 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

Post #1452195
Posted Monday, May 13, 2013 10:11 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:31 PM
Points: 41, Visits: 398
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
Post #1452200
Posted Monday, May 13, 2013 10:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, 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
Post #1452212
Posted Monday, May 13, 2013 10:32 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 20,862, Visits: 32,893
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.



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)
Post #1452213
Posted Monday, May 13, 2013 10:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, 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
Post #1452218
Posted Tuesday, May 14, 2013 12:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:05 AM
Points: 1,058, Visits: 2,697
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
Post #1452405
Posted Tuesday, May 14, 2013 2:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:03 AM
Points: 2,900, Visits: 3,301
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 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1452422
Posted Tuesday, May 14, 2013 6:37 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 20,862, Visits: 32,893
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.



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)
Post #1452538
Posted Tuesday, May 14, 2013 6:40 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 20,862, Visits: 32,893
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.



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)
Post #1452540
Posted Tuesday, May 14, 2013 8:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, 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
Post #1452633
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse