Reindexing Tables used in Log Shipping

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » Reindexing Tables used in Log Shipping

18 posts, Page 1 of 2

1 2 »»

Reindexing Tables used in Log Shipping

Rate Topic

Display Mode

Topic Options

Author

Message

homebrew01

Posted Sunday, November 18, 2007 11:45 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:37 AM
Points: 2,551, Visits: 7,201

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.

Post #423385

george sibbald

Posted Monday, November 19, 2007 6:43 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:23 AM
Points: 5,264, Visits: 11,189

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

---------------------------------------------------------------------

Post #423562

EvilPostIT

Posted Monday, November 19, 2007 8:11 AM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:35 AM
Points: 734, Visits: 636

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

Post #423622

Linda Johanning

Posted Monday, November 19, 2007 11:14 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:26 AM
Points: 330, Visits: 510

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.

Post #423759

barsuk

Posted Monday, November 19, 2007 4:07 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:26 PM
Points: 2,642, Visits: 4,960

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!)

Post #423843

Linda Johanning

Posted Tuesday, November 20, 2007 1:46 PM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:26 AM
Points: 330, Visits: 510

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....

Post #424320

barsuk

Posted Wednesday, November 21, 2007 2:19 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:26 PM
Points: 2,642, Visits: 4,960

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.

Post #424747

Paresh Randeria

Posted Friday, August 07, 2009 6:30 AM

Valued Member

Group: General Forum Members
Last Login: Friday, February 08, 2013 9:34 AM
Points: 57, Visits: 281

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.

Post #766872

barsuk

Posted Friday, August 07, 2009 6:44 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:26 PM
Points: 2,642, Visits: 4,960

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

Post #767334

Rajat Jaiswal-337252

Posted Monday, February 20, 2012 2:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:43 PM
Points: 110, Visits: 289

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.

Post #1254584

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions