Hi, Excelent script. but i add a little if to use the enterprise on line defrag
SET NOCOUNT ON
PRINT '---------------------------------------------------------------'
PRINT 'DefragIndexes.sql script started on database [' + DB_NAME() + ']'
PRINT '---------------------------------------------------------------'
PRINT ''
-- identify tables with indexes that are fragmented above 5 percent
SELECT s.Name AS SchemaName,
o.Name AS TableName,
MAX(i.avg_fragmentation_in_percent) AS [Level]
INTO #DefragLevel
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS i
INNER JOIN sys.objects AS o
ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE i.index_id > 0
AND i.avg_fragmentation_in_percent > 5
GROUP BY s.Name,
o.Name
DECLARE @Level float,
@SchemaName sysname,
@TableName sysname,
@Command nvarchar(500)
-- process each table
DECLARE DefragCursor CURSOR FOR SELECT * FROM #DefragLevel ORDER BY [Level] DESC
OPEN DefragCursor
FETCH NEXT FROM DefragCursor INTO @SchemaName, @TableName, @Level
WHILE @@FETCH_STATUS = 0 BEGIN
-- set base command
SET @Command = N'ALTER INDEX ALL ON [' + @SchemaName + '].[' + @TableName + '] '
-- reorganize indexes below 30% fragmentation or rebuild indexes above 30%
IF @Level < 30 BEGIN
SET @Command = @Command + 'REORGANIZE'
END ELSE BEGIN
SET @Command = @Command + 'REBUILD WITH (FILLFACTOR = 70, SORT_IN_TEMPDB = ON'
END
-- DAF - a los enterprise los hace on line
if patindex('%enterprise%', convert(varchar(100), SERVERPROPERTY('edition') )) > 0
begin
SET @Command = @Command + ', ONLINE = ON)'
end
else
begin
SET @Command = @Command + ', ONLINE = OFF)'
end
-- execute command
PRINT 'Defragmenting table [' + @SchemaName + '].[' + @TableName + '] from ' + CAST(@Level AS varchar(20)) + '%...'
-- print @Command
EXEC(@Command)
-- get next table to defragment
FETCH NEXT FROM DefragCursor INTO @SchemaName, @TableName, @Level
END
-- cleanup
CLOSE DefragCursor
DEALLOCATE DefragCursor
DROP TABLE #DefragLevel
-- end process
PRINT ''
PRINT '-------------------------------------------------------------------'
PRINT 'DefragIndexes.sql script finished'
PRINT '-------------------------------------------------------------------'