Defragmenting SQL Server 2008+ using ALTER INDEX

In SQL 2009 DBCC INDEXDEFRAG will no longer work as Microsoft have removed the command. You need to user ALTER INDEX instead:

CREATE PROCEDURE sjh_defragment_indexes_2008

(

    @maxfrag FLOAT

)

AS

/* Simon Hughes 15 May 2010

For SQL 2008+

This stored procedure checks index fragmentation in a database and defragments

indexes whose fragmentation fall above a specified threshold: @maxfrag

Must be run in the database to be defragmented.

exec sjh_defragment_indexes_2008 5

*/

SET NOCOUNT ON

SET XACT_ABORT ON

-- Write start time for information purposes

PRINT 'Started defragmenting at: ' + CONVERT(VARCHAR, GETDATE())

-- Check fragmentation

DECLARE @execstr NVARCHAR(255)

DECLARE lcsr CURSOR local fast_forward READ_ONLY FOR

    SELECT  'ALTER INDEX [' + i.name + '] ON [' + so.name + '] REORGANIZE;' AS execstr

    FROM    sys.sysobjects AS so

            INNER JOIN sys.dm_db_index_physical_stats(DB_ID('bybox'), NULL, NULL, NULL, 'LIMITED') AS phystat

            INNER JOIN sys.indexes AS i

                ON i.object_id = phystat.object_id

                   AND i.index_id = phystat.index_id

                ON so.name = OBJECT_NAME(i.object_id)

    WHERE   (i.name IS NOT NULL)

            AND (so.type = 'U')

            AND (phystat.avg_fragmentation_in_percent > @maxfrag)

OPEN lcsr

FETCH NEXT FROM lcsr INTO @execstr

WHILE @@FETCH_STATUS = 0

BEGIN

    EXEC sp_executesql @execstr

    FETCH NEXT FROM lcsr INTO @execstr

END

CLOSE lcsr

DEALLOCATE lcsr

-- Report on finish time for information purposes

PRINT 'Finished defragmenting at: ' + CONVERT(VARCHAR, GETDATE())

GO

Download code here.