THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Technical content about Microsoft data technologies. All opinions expressed are purely my own and do not reflect positions of my employer or associates.

Taming the Log-Eating Defrag Beast

SQL Server Standard Edition can be a beautiful thing. Since failover clustering, 64-bit support and the corresponding big memory became available with Standard Edition in 2005, it can handle most SQL workloads demanded by a small or medium business. Unless you are running a pretty huge system that truly requires Enterprise functionality like table partitioning or async database mirroring, it's often perfectly safe to run with Standard x64 on a two socket, multicore machine with enough RAM, and save a huge amount of license cost. Most of my work has been in the small/medium business space, and to be honest I've seen more cases where people shell out for Enterprise and didn't really need it than cases where people have stretched Standard Edition past its limits (with some exceptions). The most common blunder is paying to run Enterprise Edition on a storage system that's too slow - it can be far better to run Standard on adequate hardware than Enterprise on too few disks - and cheaper, to boot. But I digress.

There is a pain point with running Standard Edition systems that you want to be available near 24 x 7: index maintenance. Offline index rebuild is a blocking operation and can interfere with your applications even in the wee hours. Online index rebuild is an Enterprise Edition feature, and that's costly. Likewise, parallelism for index rebuild is Enterprise only, and index rebuild on Standard Edition can only use one processor core - so it can be much slower. See for a summary of Standard's limitations. Basically you can't rebuild indexes on Standard Edition and keep your system fully online.

It is possible to defrag indexes (and manually update statistics) instead, and do so without blocking. But there's a challenge: index defrag (now ALTER INDEX ... REORGANIZE) can produce a huge amount of transaction log, filling LDF files and/or whole log disks, so you need to pull a bit of a stunt to really make that work, or else your pager is sure to buzz in the middle of the night with a full disk, or worse, and nobody likes that!

Here's what I have done with decent success to make index defrag on Standard Edition really work:

  1. Make your usual Transaction Log Backup SQL Agent job.
  2. Make one schedule for that job on a "normal" interval like every 15 minutes. Enable this schedule.
  3. Make a second, special schedule, for the same log backup job, that runs every minute. Disable this second schedule.
  4. Name the two schedules carefully, such that you can enable and disable them by name, programmatically. I use the names "<mydatabase> Log Backup Schedule" and "<mydatabase> Defrag Log Backup Schedule"
  5. Create a script, like the one that follows, to defrag indexes that will enable the one-minute-interval log backup while doing its work, then disable it afterward. As an added precaution, have the script check how full the transaction log file is before proceeding.
  6. Test very carefully: the server where you run this must have enough disk space for log file(s) to be able to handle a little over a minute of defrag activity before the second job schedule comes on and starts taking log backups.
  7. If your testing works, then schedule this process more frequently than with index rebuilds (perhaps nightly). Why? Because with more frequent runs, there's less defrag work to do each time, and the amount of log generated is more likely to be sane. If you have a long interval between defrag runs, and your indexes become severely fragmented, then the next run has more work to do and generates more log records.

Disclaimer: this script has worked well for me, but it's somewhat complicated, and if implemented incorrectly will cause you pain and suffering. Please carefully read and understand it, and implement only after careful testing. 

Selective Defrag Script

-- Maximum duration in minutes, past which 
-- new rebuilds will not start:
DECLARE @maxDur int ;
SET @maxdur = 60 * 3 ;
-- Threshold for fragmentation in percent, above 
-- which index will defrag:
DECLARE @fragLimit int ;
SET @fragLimit = 20 ;
-- Maximum percent used for log files when starting a defrag:
DECLARE @logLimit int ;
SET @logLimit = 25 ;
-- Gather fragmentation stats for fragged indexes:
SELECT  frag.[object_id] oid,
        schema_name(o.schema_id) [schema], [table], [index],
        i.index_id indid,
        ps.function_id partitionScheme,
        frag.partition_number [partition],
        'ALTER INDEX ' + quotename( 
        + ' ON ' + quotename(schema_name(o.schema_id)) + '.'
        + quotename( ) + ' REORGANIZE'
        + CASE WHEN ps.function_id IS NOT NULL
               THEN ' PARTITION = '
                    + CAST(frag.partition_number AS varchar(100))
               ELSE ''
          END + ';' + CHAR(13) AS rebuildCmd
INTO    #fraggedIndexes
FROM    sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT,
                                       DEFAULT) frag
        INNER JOIN sys.objects AS o ON o.object_id = frag.object_id
        INNER JOIN sys.indexes AS i ON i.object_id = frag.object_id
                                       AND i.index_id = frag.index_id
        LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
WHERE   frag.avg_fragmentation_in_percent > @fragLimit
        AND i.[type] IN ( 1, 2 ) -- clustered or nonclustered, not heap or XML
        AND page_count > 100 ;
DECLARE @rebuildCmd varchar(max) ;
SET @rebuildCmd = '' ;
DECLARE @startTime datetime ;
SET @startTime = GETDATE() ;
DECLARE @logspaceused float ;
SET @logspaceused = 0 ;
DECLARE @logBackupSchedName varchar(128);
SET @logBackupSchedName = db_name() + ' Defrag Log Backup Schedule';
CREATE TABLE #logspace (
     [Database Name] varchar(128),
     [Log Size (MB)] float,
     [Log Space Used (%)] float,
     [Status] int
    ) ;
-- Check how much of the transaction log is in use (prevent filling the log)
INSERT  #logspace
SELECT  @logspaceused = [Log Space Used (%)]
FROM    #logspace
WHERE   [Database Name] = DB_NAME() ;
DELETE  #logspace ;
-- Work on the queue of indexes until the allowed time is exceeded
-- or the transaction log file is too full:
               FROM     #fraggedIndexes )
    AND @logspaceused < @logLimit
    AND DATEDIFF(minute, @startTime, GETDATE()) < @maxDur 
        -- Enable frequent log backup schedule 
        -- while doing index maintenance
        EXEC msdb.dbo.sp_update_schedule 
            @name = @logBackupSchedName,
            @enabled = 1 ;
        SELECT TOP 1
                @rebuildCmd = rebuildCmd
        FROM    #fraggedIndexes
        ORDER BY page_count * avg_fragmentation_in_percent DESC ;
        PRINT GETDATE() ;
        PRINT @rebuildCmd ;
        EXEC ( @rebuildCmd ) ;
        DELETE  FROM #fraggedIndexes
        WHERE   rebuildCmd = @rebuildCmd ;
        INSERT  #logspace
        SELECT  @logspaceused = [Log Space Used (%)]
        FROM    #logspace
        WHERE   [Database Name] = DB_NAME() ;
        DELETE  #logspace ;
    END ;
-- Disable frequent log backup schedule after index maintenance
EXEC msdb.dbo.sp_update_schedule 
    @name = @logBackupSchedName,
    @enabled = 0 ;
DROP TABLE #logspace ;
DROP TABLE #fraggedIndexes ;


Published Tuesday, July 27, 2010 3:20 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Uri Dimant said:

Hi Merrill

Generating ALTER INDEX command on pretty big database takes very very long time. All default parameters should be replaced with table/index id ..Have you considered using Ola script to maintain indexes

July 28, 2010 2:34 AM

tosc said:

Hi Merrill,

thanks for your smart description of small and medium business needs in SQL Server, which is consistent with my experience.

I wish you a nice day,


July 28, 2010 2:50 AM

merrillaldrich said:

Hi Uri - I have looked at Ola's solution, and it's terrific. He's a rock star for providing that to the community, and everyone should have a look at it.

I didn't implement his scripts in my company for a variety of reasons, mostly to do with specifics of our SQL Server environment.

July 28, 2010 4:38 PM

Bob Barrows said:

It appears you left out the bit for enabling the defrag log backup schedule

August 2, 2010 10:02 AM

Bob Barrows said:

Oh, never mind, I see it now ... inside the loop? Why enable it with every pass through the loop? I would have expected you to enable it, then start looping through the fragged indexes. Maybe I'm missing something else.

August 2, 2010 10:06 AM

merrillaldrich said:

@Bob - you're right. It'd be clearer ahead of the loop than inside. I think I was trying to avoid repeating the conditions in the 'while' -- i.e. if (there's work to do) enable the job; while (there's work to do) process the queue -- which is probably silly.

August 2, 2010 12:16 PM

Ola Hallengren said:

Another option is to have an Alert on [Percent Log Used] and let that execute the transaction log backup job as response.

August 2, 2010 2:11 PM

merrillaldrich said:

Också en smart idé! (It's Ola himself! :-)

August 5, 2010 12:00 PM

Merrill Aldrich said:

So, last day of the year, and I can see many people are in a reflective mood. I don’t usually deep dive

December 31, 2010 8:27 PM

Leave a Comment


This Blog


Privacy Statement