InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 15:20:24 GMT20http://www.sqlservercentral.com/Forums/Topic423385-5-1.aspx[quote][b]GilaMonster (2/15/2013)[/b][hr][quote][b]jblovesthegym (4/18/2012)[/b][hr]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. [/quote]Worth noting that the info given in the post you mention is lacking a few very critical points.Maybe take a read through this before you go off and use bulk logged recovery. It does have some downsides, it's not always interchangeable with full recovery (eg when there's mirroring), there are some risks to running it and it won't reduce the size of your log backups.[url]http://www.sqlservercentral.com/articles/Recovery+Model/89664/[/url][/quote]You are right Gail. Indeed, I should have added in my post that using the bulk logged recovery model will not reduce the size of log backups. It will only protect the transaction log files from growing very large without breaking the log chain.Wed, 20 Feb 2013 01:25:48 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspx[quote][b]jblovesthegym (4/18/2012)[/b][hr]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. [/quote]Worth noting that the info given in the post you mention is lacking a few very critical points.Maybe take a read through this before you go off and use bulk logged recovery. It does have some downsides, it's not always interchangeable with full recovery (eg when there's mirroring), there are some risks to running it and it won't reduce the size of your log backups.[url]http://www.sqlservercentral.com/articles/Recovery+Model/89664/[/url]Fri, 15 Feb 2013 01:01:39 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxI also have a very large database with very sizeable clustered indexed tables. I've been looking for a way around the bloating of logs and keeping tranlog backups at a decent size. The only way I have found to do this efficiently is to spread the workload throughout the week instead of doing one-shot reindexing/reorg of the database on the weekend.Also, one comment about Devine's post. Step #2 is fine, but remember that switching to bulk-logged recovery most often increases the size of the transaction log backups (although it does keep transaction log size smaller). This is because the pages that are modified during reindexing are marked in the transaction log, then when the tranlog backups occur .. then all of the pages marked are copied into the tranlog backup. Even when using 2008+ compression, redgate, or litespeed .. these log backups can very easily clog a WAN connection.Thu, 14 Feb 2013 20:31:55 GMThipyskipyhttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspx[quote][b]GilaMonster (2/20/2012)[/b][hr]Please note: 5 year old thread.[/quote]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. :)Wed, 18 Apr 2012 07:22:47 GMTjblovesthegymhttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxActuly 2 new posts got added in the same thread today prior to my response, so replying to them :-)Mon, 20 Feb 2012 04:06:20 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxPlease note: 5 year old thread.Mon, 20 Feb 2012 03:54:56 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxI 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.Mon, 20 Feb 2012 03:45:40 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxLog Ship will be insync once you follow steps. I suggest, pl verify on test server for surity. We always do.Mon, 20 Feb 2012 02:42:18 GMTParesh Randeriahttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxHi 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.Mon, 20 Feb 2012 02:01:08 GMTRajat Jaiswal-337252http://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxyou 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 serverFri, 07 Aug 2009 18:44:19 GMTbarsukhttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxNO 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. [b]Differential Backup on Primary[/b];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. Fri, 07 Aug 2009 06:30:29 GMTParesh Randeriahttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxIt 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 unacceptableThe 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.Wed, 21 Nov 2007 14:19:14 GMTbarsukhttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxWhen 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....Tue, 20 Nov 2007 13:46:44 GMTLinda Johanninghttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxAt 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!)Mon, 19 Nov 2007 16:07:10 GMTbarsukhttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxWhat 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.Mon, 19 Nov 2007 11:14:34 GMTLinda Johanninghttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxIf you run the optimizations more often the should less load to have to ship across.Mon, 19 Nov 2007 08:11:18 GMTEvilPostIThttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxsetting 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 databaseMon, 19 Nov 2007 06:43:36 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic423385-5-1.aspxI 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.Sun, 18 Nov 2007 11:45:51 GMThomebrew01