February 14, 2013 at 8:31 pm
I 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.
February 15, 2013 at 1:01 am
jblovesthegym (4/18/2012)
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.
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.
http://www.sqlservercentral.com/articles/Recovery+Model/89664/
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, 2013 at 1:25 am
GilaMonster (2/15/2013)
jblovesthegym (4/18/2012)
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.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.
http://www.sqlservercentral.com/articles/Recovery+Model/89664/
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.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply