Disable Log Shipping 2000 while Index rebuild Jobs are running

SQL Server 2000 Enterprise Editions Log Shipping was setup using Database Maintenance Plans

Transaction 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?
more ▼

asked Jul 28, 2010 at 09:42 AM in Default

rajDBA gravatar image

49 4 5 6

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

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][2] 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:

/* Minimum Fragementation level used to drive index rebuild */
DECLARE @MinFragmentation tinyint
SELECT @MinFragmentation = 30

DECLARE @RebuildCommand varchar(8000)
SELECT @RebuildCommand = ''
/* Temp table for Index Interrogation */
    (ObjectName sysname,
     ObjectId int,
     IndexName sysname,
     Indexid tinyint,
     [Level] int,
     Pages int,
     [Rows] int,
     MinimumRecordSize int,
     MaximumRecordSize int,
     AverageRecordSize int,
     ForwardedRecords int,
     Extents int,
     ExtentSwitches int,
     AverageFreeBytes int,
     AveragePageDensity int,
     ScanDensity int,
     BestCount int,
     ActualCount int,
     LogFragmentation tinyint,
     ExtentFragmentation int)

/* Interrogate Indexes and store info in temp table */
INSERT  #Results
        EXEC ('DBCC SHOWCONTIG With TableResults')

/* Build Command to Reindex Indexes */
SELECT  @RebuildCommand = @RebuildCommand + 'DBCC DBREINDEX (''' + ObjectName + ''' , ' + IndexName + ');'
FROM    #Results R
INNER JOIN sysindexes si ON si.id = r.ObjectId
                            AND si.indid = r.Indexid
INNER JOIN sysobjects so ON so.id = si.id
WHERE   so.type = 'U'
        AND Indexid > 0
        AND LogFragmentation > @MinFragmentation

/* Execute Rebuild Command */
--SELECT @RebuildCommand
EXEC (@RebuildCommand)

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.

[2]: http://sqlfool.com/2010/04/index-defrag-script-v4-0/
more ▼

answered Jul 30, 2010 at 02:48 AM

WilliamD gravatar image

25.9k 17 19 41

+1 That's a bit more constructive than my answer!
Jul 30, 2010 at 03:14 AM David Wimbush
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Jul 30, 2010 at 12:35 AM

David Wimbush gravatar image

David Wimbush
5.1k 29 31 34

(comments are locked)
10|1200 characters needed characters left

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

answered Jul 30, 2010 at 05:33 AM

Blackhawk-17 gravatar image

11.9k 28 31 37

Thanks for the Information Provided. I am planning to disable the Transaction Log Backup job using TSQL in a new Job

Two seperate jobs will do the enable and disabling jobs during the Maintenance Period.

--First Job to Disable Transaction Log Backup -- To Diable Job

exec msdb..sp_update_job @job_name = 'Job_name', @enabled = 0

-- Second Job to Enable Transaction Log Backup --To Enable Job

exec msdb..sp_update_job @job_name = ''Job_name', @enabled = 1
Jul 30, 2010 at 02:04 PM rajDBA
I think this will still create huge Log Backup file once I enable the job after the Maintenance jobs ar completed
Jul 30, 2010 at 02:10 PM rajDBA
The log backups are big because your maintenance jobs are changing a lot of data. You have to either fix that or accept that you will have X MB of log records to backup and ship at some point. If you suspend it while the maintenance jobs are running you will have an even bigger log backup afterwards. Do you actually have a problem? Are your maintenance jobs being slowed down by the log shipping? Are you running out of space to hold the transaction log files? Are you running out of space for the log backup files? What are you trying to fix here?
Jul 30, 2010 at 11:15 PM David Wimbush
We are running out of Space in one of the servers,where we are doing the transactional log backups?
Aug 03, 2010 at 12:00 PM rajDBA
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 28, 2010 at 09:42 AM

Seen: 1647 times

Last Updated: Jul 28, 2010 at 09:42 AM