IndexOptimize - Log Growth

Question

IndexOptimize - Log Growth

Kitch

Old Hand

Points: 334
More actions
September 16, 2014 at 1:19 pm

#293182

Hi All,
I'm implementing Ola's maintenance solution on a 2008 R2 (SP2) instance. When running the IndexOptimize feature on a 52 GB database the log grows by 12GB. The database is in full recovery mode with 1 daily full backup followed by hourly transaction log backups.
The sqlcmd -Q arg is:
EXECUTE dbo.IndexOptimize
@databases = 'MY_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y'
The specific database is designed pretty good with no obvious faults. However, it was neglected for about two years and was highly fragmented when I inherited it. I've used Ola's script to get fragmentation and statistics under control. I am at the point where the IndexOptimize is ready to be scheduled for a weekly run.
Question - is this amount of log growth normal (12 GB on a 52 GB database)? Is there anything else I should look for to reduce the amount of log growth - given the past neglect?
Thanks in advance for any and all feedback,
Kitch

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 1

It all depends on how much work the IndexOptimize job has to do. One way to check would be to run the following script:-

USE [<DB NAME>];

GO

SELECT

DB_NAME(DB_ID()) AS [Database Name],

[schemas].name AS [SchemaName],

[objects].name AS [TableName],

[indexes].name AS [IndexName],

[dm_db_index_physical_stats].index_depth,

[dm_db_index_physical_stats].avg_fragmentation_in_percent,

[dm_db_index_physical_stats].fragment_count,

[dm_db_index_physical_stats].avg_fragment_size_in_pages,

[dm_db_index_physical_stats].page_count,

GETUTCDATE() AS [CaptureDate]

FROM

[sys].[dm_db_index_physical_stats](DB_ID(),NULL,NULL,NULL,'LIMITED' ) AS [dm_db_index_physical_stats]

INNER JOIN

[sys].[objects] AS [objects] ON ([dm_db_index_physical_stats].object_id = [objects].object_id)

INNER JOIN

[sys].[schemas] AS [schemas] ON ([objects].[schema_id] = [schemas].[schema_id])

INNER JOIN

[sys].[indexes] AS [indexes] ON ([dm_db_index_physical_stats].object_id = [indexes].object_id

AND [dm_db_index_physical_stats].index_id = [indexes].index_id )

Be careful, this can take some time (run it on a dev system first)! But it will show you the fragmentation of all your indexes. If you schedule this to run at intervals you can see the how your indexes fragment over time.

You can then adjust your maintenance to compensate, maybe running REORGANIZE more frequently would be better?

Andrew

You can run this in pers

ScottPletcher SSC Guru Points: 100946 More actions · Answer 2

The more disorganized the index, the more work space will be needed. Hopefully once you get the tables back in shape, the future runs won't be as bad.

Also, look into specifying SORT_IN_TEMPDB, which can reduce data (not log) space usage in your main db.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Kitch Old Hand Points: 334 More actions · Answer 3

Thanks everyone. All good now. There was one particular index that needed some TLC. Also sized the log appropriately and set its autogrow to an appropriate value. It was set at the 10% default.

Thanks Again!