August 13, 2009 at 9:53 am
Comments posted to this topic are about the item Defragmenting and rebuilding indexes
August 26, 2009 at 1:27 pm
When I ran the script, I received this error:
Msg 102, Level 15, State 1, Line 27
Incorrect syntax near '('.
Line 27:
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS i
Please help.
Thanks.
Scott
September 28, 2009 at 5:51 pm
Hi there,
the script will be better if the current fillfactor for each index is saved and reused when the indexes are rebuild.
December 8, 2009 at 3:03 pm
You should add row-count in the whereclause because defrag on small table will not help much.
Jason
http://dbace.us
😛
January 9, 2013 at 5:29 am
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 '-------------------------------------------------------------------'
January 9, 2013 at 5:32 am
ssaari (8/26/2009)
When I ran the script, I received this error:Msg 102, Level 15, State 1, Line 27
Incorrect syntax near '('.
Line 27:
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS i
Please help.
Thanks.
Scott
If you are using SQL 2000 (80) the script fail with this message. The same problem appears in 2005 when the master db is in level 80, may be because its a server has been updated in place from a 2000.
May 2, 2016 at 4:17 pm
Thanks for the script.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply