Click here to monitor SSC
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
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2980 Visits: 9071
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
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
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
Right there with Babe
Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)

Group: General Forum Members
Points: 734 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
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 620
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3829 Visits: 6863
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
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 620
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3829 Visits: 6863
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
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3829 Visits: 6863
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-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 391
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