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 (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)

Group: General Forum Members
Points: 780 Visits: 620
Hi Durai

thanks for the input

Jim
JC-3113
JC-3113
Right there with Babe
Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)

Group: General Forum Members
Points: 780 Visits: 620
Understood Lynn

Thanks
Jim
JC-3113
JC-3113
Right there with Babe
Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)

Group: General Forum Members
Points: 780 Visits: 620
Thanks Lynn

can you tell me when it is best to update indexes ?
before a full backup or after ?
or do I need to do one before and after



Thanbks
Jim
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8109 Visits: 9974
JC-3113 (5/14/2013)
Thanks Lynn

can you tell me when it is best to update indexes ?
before a full backup or after ?
or do I need to do one before and after

Thanbks
Jim


You will probably get several different answers - all of which are valid...

I prefer rebuilding indexes prior to performing a backup. But I make sure that if that process fails it does not prevent the backup from being performed.

Others will prefer to follow the backup and rebuild indexes...

And yet, others prefer separating these out as completely separate operations. That is, a job to rebuild indexes could be scheduled to run at 1am and the backups scheduled to run at the same time - or completely different times with no dependencies.

I use this last option when I can no longer meet the maintenance window.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

JC-3113
JC-3113
Right there with Babe
Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)

Group: General Forum Members
Points: 780 Visits: 620
Hi Jeffrey

thanks for the input

that takes me back to my original issue
I am rebuilding indexes on sat night after my full back up

what happens then is that my differentials are huge because of the rebuild
(every 4 hours)

so i was going to rebuild the indexes before the full to alleviate the size of the differentials that follow..

Jim
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41426 Visits: 38567
JC-3113 (5/14/2013)
Hi Jeffrey

thanks for the input

that takes me back to my original issue
I am rebuilding indexes on sat night after my full back up

what happens then is that my differentials are huge because of the rebuild
(every 4 hours)

so i was going to rebuild the indexes before the full to alleviate the size of the differentials that follow..

Jim


This is a decision you have to make, we can't make it for you. If you are rebuilding every index on Saturday night, then you may want to run a differential prior to the rebuilds (gives you a restart point if there is a problem during the rebuilds) and then a full backup when they are completed.

This has to be your decision and you have to support it to your boss.

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)
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1677 Visits: 2775
JC-3113 (5/14/2013)
Hi Jeffrey

thanks for the input

that takes me back to my original issue
I am rebuilding indexes on sat night after my full back up

what happens then is that my differentials are huge because of the rebuild
(every 4 hours)

so i was going to rebuild the indexes before the full to alleviate the size of the differentials that follow..

Jim


As lynn says it wont allow point in time recovery why not switching it to only Log backup and keep your full backup after index rebuild.

This is just my suggestion.

Regards
Durai Nagarajan
JC-3113
JC-3113
Right there with Babe
Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)

Group: General Forum Members
Points: 780 Visits: 620
Thanks Lynn

Jim
simon.murin
simon.murin
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 575
The best way to save backup space would be to rebuild your indexes before the full backup.
JC-3113
JC-3113
Right there with Babe
Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)

Group: General Forum Members
Points: 780 Visits: 620
Thanks Simon

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