November 18, 2007 at 11:45 am
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.
November 19, 2007 at 6:43 am
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
---------------------------------------------------------------------
November 19, 2007 at 8:11 am
If you run the optimizations more often the should less load to have to ship across.
November 19, 2007 at 11:14 am
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.
November 19, 2007 at 4:07 pm
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!)
November 20, 2007 at 1:46 pm
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....
November 21, 2007 at 2:19 pm
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.
August 7, 2009 at 6:30 am
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.
August 7, 2009 at 6:44 pm
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
February 20, 2012 at 2:01 am
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.
February 20, 2012 at 2:42 am
Log Ship will be insync once you follow steps.
I suggest, pl verify on test server for surity. We always do.
February 20, 2012 at 3:45 am
I am not sure why people are so much ineterested in breaking log chain by changing the reocovery model to SIMPLE, just to avoid the log file growth during Re-indexing.
Use Bulk Logged Recovery Model instead to avoid the log file growth.
Benefits:
1. Full & Bulk Logged recovery models are interchangable. Interchanging Full & Bulk Logged recovery model doesn't break the log chain.
2. Once the reindexing is done you don't need to take the full/diff backups & restoring them on secondary.
3. You just need to switch back to Full recovery model again after the reindexing is done. Log backups will continue successfully after that & you should leave it all to SQL Server log shipping jobs.
February 20, 2012 at 3:54 am
Please note: 5 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2012 at 4:06 am
Actuly 2 new posts got added in the same thread today prior to my response, so replying to them 🙂
April 18, 2012 at 7:22 am
GilaMonster (2/20/2012)
Please note: 5 year old thread.
SOME POSTS in this thread are indeed over two years old, but the one immediately preceding yours answers a lot of questions I was going to pose (as I am looking after log shipping of over 50 dbs on SS2K) on the forum.
Kudos to the man. 🙂
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply