Thanks Adams for your help.
I've solved my problem.
I don't know why, but some indexes of my SQL2005 DB listed in sys.objects have the field parent_object_id = 0 in sys_objects.
That why the script in my first post didn't manage some very important indexes.
I've corrected my script with Microsoft recommandations in
(http://msdn2.microsoft.com/en-us/library/ms188917.aspx)
and now, all my indexes are managed by my script.
So, find enclosed the corrected script :
DECLARE @base VARCHAR(100),@diff VARCHAR(100), @date_actuelle datetime,@command VARCHAR(8000),@pre_name_table VARCHAR(150),@name_index VARCHAR(150),@name_table VARCHAR(150),@id_index int,@avg_fragmentation_in_percent int
DECLARE @i int
SET @i = 0
SET @date_actuelle = GETDATE()
DECLARE name_index CURSOR FOR
SELECT
a.index_id,
b.name,
avg_fragmentation_in_percent,
c.name,
d.name,
e.name
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
JOIN sys.objects AS c
ON c.object_id =a.object_id
JOIN sys.schemas AS d
ON d.schema_id = c.schema_id
JOIN sys.databases AS e
ON e.database_id = DB_ID()
WHERE a.avg_fragmentation_in_percent BETWEEN 5 AND 100 AND a.index_id > 0 AND a.page_count > 8 ORDER BY a.avg_fragmentation_in_percent;
OPEN name_index
FETCH name_index INTO @id_index, @name_index, @avg_fragmentation_in_percent, @name_table,@pre_name_table, @base
SELECT @command = N'ALTER DATABASE '+ @base + N' SET RECOVERY BULK_LOGGED';
PRINT @command
EXEC(@command);
PRINT ''
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Nom de l index : '+@name_index
PRINT ''
IF @avg_fragmentation_in_percent <= 30
BEGIN
PRINT 'TYPE DE REINDEXATION : ALTER REORG'
PRINT convert(varchar,GETDATE(),103) + ' ' + convert(varchar,GETDATE(),14)
SELECT @command = 'ALTER INDEX [' + @name_index + '] ON [' + @pre_name_table + '].[' + @name_table + '] REORGANIZE WITH (LOB_COMPACTION=ON)' ;
PRINT @command
PRINT '-------------------------------------------------------------------------'
PRINT ''
SET @i=@i+1
EXEC (@command);
END
IF @avg_fragmentation_in_percent >= 30
BEGIN
PRINT 'TYPE DE REINDEXATION : ALTER REBUILD'
PRINT convert(varchar,GETDATE(),103) + ' ' + convert(varchar,GETDATE(),14)
SELECT @command = 'ALTER INDEX [' + @name_index + '] ON [' + @pre_name_table + '].[' + @name_table + '] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF, MAXDOP=0, SORT_IN_TEMPDB=ON )';
PRINT @command
PRINT '-------------------------------------------------------------------------'
PRINT ''
SET @i=@i+1
EXEC (@command);
END
FETCH name_index INTO @id_index,@name_index,@avg_fragmentation_in_percent,@name_table,@pre_name_table, @base
END
PRINT 'FIN DE LA REINDEXATION'
PRINT convert(varchar,GETDATE(),103) + ' ' + convert(varchar,GETDATE(),14)
SELECT @command = N'ALTER DATABASE '+ @base + N' SET RECOVERY FULL';
PRINT @command
EXEC(@command);
CLOSE name_index
DEALLOCATE name_index
PRINT ''
PRINT N'Nombre d index réindexé : '+cast(@i AS varchar(50))
SELECT @diff = DATEDIFF(ss,@date_actuelle,GETDATE())
PRINT N'Temps de maintenance : '+ @diff +N' s'
Littlesquall
😉