Rebuilding Database Indexes

Over time some of the SQL Server database indexes may need to be rebuilt to ensure optimum performance.

SpiraTeam v5.0 or Later

From v5.0 onwards, we have added a built-in option within the Administration > Data Tools section to reindex the underlying database:

In the latest versions:

Just click the button and the system will perform the reindex for you.

SpiraTeam v4.2 or Earlier

To rebuild all the indexes in the SpiraTeam database, you need to run the following database command through SQL Query Analyzer / Management Studio whilst all users are logged off the application. For SQL 2005, 2008, or 2012, run the following SQL command:

SET NOCOUNT ON
GO

DECLARE @FillFactor TINYINT
SELECT @FillFactor = 80
DECLARE @StartTime DATETIME
SELECT @StartTime = GETDATE()

IF object_id('tempdb..#TablesToRebuildIndex') IS NOT NULL
BEGIN
            DROP TABLE #TablesToRebuildIndex
END

DECLARE @NumTables VARCHAR(20)
SELECT s.[Name] AS SchemaName
            ,t.[name] AS TableName
            ,SUM(p.rows) AS RowsInTable
INTO #TablesToRebuildIndex
FROM sys.schemas s
LEFT JOIN sys.tables t ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions p ON t.object_id = p.object_id
LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE p.index_id IN (0,1)
            AND p.rows IS NOT NULL
            AND a.type = 1
GROUP BY s.[Name] ,t.[name]
SELECT @NumTables = @@ROWCOUNT

DECLARE RebuildIndex CURSOR FOR
SELECT ROW_NUMBER() OVER (ORDER BY ttus.RowsInTable)
            ,ttus.SchemaName
            ,ttus.TableName
            ,ttus.RowsInTable
FROM #TablesToRebuildIndex AS ttus
ORDER BY ttus.RowsInTable

OPEN RebuildIndex

DECLARE @TableNumber VARCHAR(20)
DECLARE @SchemaName NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @RowsInTable VARCHAR(20)
DECLARE @Statement NVARCHAR(300)
DECLARE @Status NVARCHAR(300)

FETCH NEXT
FROM RebuildIndex
INTO @TableNumber
            ,@SchemaName
            ,@tablename
            ,@RowsInTable

WHILE (@@FETCH_STATUS = 0)
BEGIN
            SET @Status = 'Table ' + @TableNumber + ' of ' + @NumTables + ': Rebuilding indexes on ' + @SchemaName + '.' + @tablename + ' (' + @RowsInTable + ' rows)'
            RAISERROR (@Status,0,1)       WITH NOWAIT

            SET @Statement = 'ALTER INDEX ALL ON [' + @SchemaName + '].[' + @tablename + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3), @FillFactor) + ' )'
            EXEC sp_executesql @Statement

            FETCH NEXT
            FROM RebuildIndex
            INTO @TableNumber
                        ,@SchemaName
                        ,@tablename
                        ,@RowsInTable
END
CLOSE RebuildIndex
DEALLOCATE RebuildIndex
DROP TABLE #TablesToRebuildIndex
PRINT 'Total Elapsed Time: ' + CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE())) + ' minutes'
GO

If you are on a version of SQL Server before 2005, run the following SQL.
Note, however, that this command will not give update messages, nor will it display any errors if reindexing errors or cannot be completed because tables are in use:

EXEC sp_MSforeachtable 'alter index all on ? rebuild'

You can download the full SQL file from this KB article, or copy and paste the SQL from above.
Modifications of the SQL could damage or cause data loss, execute modified versions of these commands at your own risk!