September 27, 2004 at 12:08 pm
Hi,
I have a 15 GB database and I make a transaction log backup every hours. The size to the transaction backup is on average 500 KB.However, during the optimization plan (rebuilding indexes which runs for about 50 min.), the transaction log reaches 13 GB. This messes up my Log Shipping plans!
Any suggestions on this issue (Note that I am using the Bulk-Logged option).
September 28, 2004 at 5:45 am
It's a real pain isn't it? The only way around this is to stagger the index rebuilds to fit in with the logs so you generate a series of logs rather than one large log.
Previously when I'd used my own version of log shipping I used to stop the shipping, put the db in simple model for optimisation, afterwards I'd turn it back to full and copy a backup and start shipping again.
As far as I can see ms log shipping isn't flexible enough to cope with this scenario so assuming you have plenty of time to copy a backup and restore it I suggest you write your own version of log shipping ( pretty easy to do - use robocopy for the log copy )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 28, 2004 at 7:59 am
I implemented a 'home made' log shipping solution for my company's Disaster Recovery environment after I found the official SQL Server one to be too inflexible.
Basically I use a combination of full, differential and transaction log backups copied to the DR servers and restored in standby mode (RESTORE DATABASE.....WITH STANDBY). The largest dbs (100Gb) are fully backed up weekly, differentially backed up once or twice a day and the logs are backed up every hour or so.
You should follow a database reindex with a full backup, diffs and tx log backups will be just as large (as you have discovered!). Apart from that experiment with differential backups, smaller than full and easier to apply than tranaction log backups
September 28, 2004 at 8:50 am
I assume that doing the rebuild of the indexes BEFORE doing a full backup will still not reduce the size of a log backup, right?
September 28, 2004 at 9:07 am
It's one of the downsides of a 24x7 that the housekeeping becomes difficult. Careful design ( of database structures ) to minimise fragmentation will allow the use of online defrag which cause less big transaction logs.
I resolved the problem for one database by spreading the index rebuilds over 7 days and putting waits betwwen index rebuilds to minimise the impact on individual logs.
You're right, the ms log shipping is not as flexible as homegrown.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply