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


Reindexing Tables used in Log Shipping


Reindexing Tables used in Log Shipping

Author
Message
homebrew01
homebrew01
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12872 Visits: 9222
I have several databases just set up for log shipping to another server. Previously, I would set the DB to SIMPLE recovery, run DBCC DBREINDEX, then set it back to FULL recovery.

But now that they're used for Log Shipping, I imagine that my previous strategy would break the transaction log chain. If I run DBCC DBREINDEX in FULL mode, then I'll generate big transaction logs, and those T-Logs will get pushed to the secondary server.

What's the best practice for doing DB maintenance for databases used in Log Shipping ?? Thanks in advance.



george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25750 Visits: 13701
setting to simple mode will break the log chain, so that is out for log shipping. I suggest you maintain the database to whatever degree it requires and ignore the fact it is log shipping, just take more frequent tran log backups during index rebuilds\reorgs.

One thing you can do is run integrity checks (checkdbs) on the secondary database to take some load off the live database

---------------------------------------------------------------------
EvilPostIT
EvilPostIT
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1116 Visits: 648
If you run the optimizations more often the should less load to have to ship across.



Nuke the site from orbit, its the only way to be sure... w00t
Linda Johanning
Linda Johanning
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1467 Visits: 625
What I've had to do is split up the reindex or defrag jobs so that there aren't so many large log files to ship to the secondary. I put 6 of our largest tables to be defragged on separate days and then on the 7th day do the rest of them. This doesn't solve the problem, but it helps. Also, try to stay with re-indexing. Defragging large tables takes twice as long and creates much larger log files.



barsuk
barsuk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5380 Visits: 7019
At my prior job, on the wknds we were running the following on Saturday night:
Stop Logshipping;
Set Recovery mode to Simple;
Reindex;
Update Stats for some indexes;
Flip Recovery mode to Full;
Backup;
Copy and Restore on Secondary;
Start applying Logs on Secondary.
Everybody recognized the performance issues when we missed week or two without reindexing, so flipping between Recovery Modes and Restoring to the Secondary of the Backup was always a priority. Few times when we forgot to set Recovery to simple on weekend ( 3AM Sunday, tran log dump grew to 70GB-try to copy it to the Secondary!)



Linda Johanning
Linda Johanning
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1467 Visits: 625
When you say the statement below, you were basically rebuilding Log Shipping every weekend.

Backup;
Copy and Restore on Secondary;
Start applying Logs on Secondary.

When you have a very large database, rebuilding Log Shipping on a regular basis isn't do-able--especially when you don't have a large window to do it in. One time I can remember that it took about 5 hours just to copy a set of 6 backup files between servers....



barsuk
barsuk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5380 Visits: 7019
It has been awhile, but backup took about 1.5hr on 1.3TB Database. Copy another few hours and restore a bit longer. Secondary server was not required right away, so once backup was completed ( we skipped verify part) on Primary, it was available to use.
At that time I was having tables with few hundred mln rows, a lot of writes, so missing one wknd for reindexing was unacceptable.
By the way I was using LS for backup and restore. Without it, the above scenario would be unacceptable
The alternative to the that scenation would be using BCV\Time Finders Snaps, but it is very expensive from hardware point of view and require a lot of tweaking.



Paresh Randeria
Paresh Randeria
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 322
NO NEED TO BACKUP AND RESTORE FULL DATABASE. Differential will do...

1. FULL BACKUP;
2. Let One Log Backup and Restore on Secondary;
3. DISABLE Log BACKUP (DO NOT STOP LOG SHIPPING, DO NOT CHANGE RECOVERY MODEL);
4. Reindex;
5. Update Stats;
6. DUMP TRAN on Primary;
7. Differential Backup on Primary;
8. Copy and Restore Differential WITH NO RECOVERY/STAND BY on Secondary;
9. Start Backup Logs on Primary;
10. Copy and Start applying Logs on Secondary.
barsuk
barsuk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5380 Visits: 7019
you cannot run reindex on a DB with TB of data in a Full recovery mode. It will blow up your Tran Log dramatically. One weekend one of my coworkers forgot to reset it to simple before running re-indexing, so we ended up having a tran log dump of 64GB which we had to copy to secondary server



Rajat Jaiswal-337252
Rajat Jaiswal-337252
SSC-Addicted
SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)

Group: General Forum Members
Points: 409 Visits: 398
Hi Paresh,
I like your solution but again i am bit hasy in applying in production before testing it local.
Are you sure log shipping will not affected after this process because my production database is very large and it takes quite long time to backup and restore on secondary server.
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