SQL Server 2000 Enterprise Editions Log Shipping was setup using Database Maintenance PlansTransaction Log backup runs for evry 15 minutes.But during weekend while Maintenance jobs are running these Log backups grows huge. Is there any other way to disable the Log Backup at that interval?
asked Jul 28 '10 at 09:42 AM in Default
I would rather recommend that you change the way you do your index maintenance, rebuilding only when necessary will drastically reduce log file growth issues.
There are a number of "smart" index maintenance scripts available on the internet, my personal favourite is supplied by Ola Hallengren. There is also [Michelle Ufford's Index Defrag v4.0] which is also very good.
However, as you are on SQL 2000, you are limited in you index interrogation abilities. Something like this would at least get you started:
There is no error handling and it could be done better, but it is a starting point to getting your index rebuilds to only work on indexes that really need work.: http://sqlfool.com/2010/04/index-defrag-script-v4-0/
answered Jul 30 '10 at 02:48 AM
The problem is that maintenance jobs update a lot of pages and all those changes get recorded in the transaction log. I'm afraid there isn't a way round it if you want to log ship. In fact the log shipping is probably stopping your logs from getting even bigger because the frequent backups mark space in the log file for re-use, preventing file growth.
answered Jul 30 '10 at 12:35 AM
Unless you are willing to re-initialize Log Shipping after every Maintenance run then the short answer is no.
Log Shipping faithfully applies logged transactions to ensure synchronization. About the only workarounds I can think of would be to drop the indexes and then re-create them (though that is basically what a rebuild does anyway) or re-evaluate and reduce your indexes (which of course may impact your performance).I'm afraid you will have to accept the log sizes as an artifact of Log Shipping and the size of your data. If you want smaller logs - ship more often, every 10 or 5 minutes.
answered Jul 30 '10 at 05:33 AM