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 Tuesday, May 14, 2013 8:44 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 Durai

thanks for the input

Jim
Post #1452634
Posted Tuesday, May 14, 2013 8:45 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
Understood Lynn

Thanks
Jim
Post #1452635
Posted Tuesday, May 14, 2013 8:47 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 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
Post #1452636
Posted Tuesday, May 14, 2013 12:14 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:31 AM
Points: 4,358, Visits: 9,537
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1452757
Posted Tuesday, May 14, 2013 2:16 PM
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 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
Post #1452825
Posted Tuesday, May 14, 2013 2:28 PM


SSC-Insane

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

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



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 #1452831
Posted Tuesday, May 14, 2013 9:14 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1452905
Posted Wednesday, May 15, 2013 9:03 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 Lynn

Jim
Post #1453165
Posted Thursday, May 16, 2013 12:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 77, Visits: 569
The best way to save backup space would be to rebuild your indexes before the full backup.

Post #1453332
Posted Thursday, May 16, 2013 7:47 PM
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 Simon

Jim
Post #1453821
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse