Rebuilding Indexes and Shrinking Data Files

 

Indexes should periodically be rebuilt to reorganize pages, remove physical fragmentation and recalculate statistics. Shrinking the database improves performance.  Linko recommends doing this monthly.  Use the script below to rebuild indexes and shrink the data files.  As with all the procedures outlined in this document this procedure should only be done while no one is in Linko.

 

USE CTS_Data

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR

SELECT table_name FROM information_schema.tables

WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

DBCC DBREINDEX(@TableName,' ',0)

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

GO

Exec sp_updatestats

GO

DBCC SHRINKDATABASE (CTS_Data, 10)

GO

 

Notes on Indexes:

 

1.       As a possible additional maintenance step, Linko suggests that Indexes should occasionally be rebuilt (monthly) using DROP_EXISTING and FILLFACTOR with recommended fill factors to reorganize pages, remove physical fragmentation and recalculate statistics. Linko has a specially designed script to do this for each Linko Upgrade called the CTS_Data_Rebuild_Indexes.sql script.

 

2.       FILLFACTOR will reduce page splitting and allocate additional space to tables that frequently have new data added to them.

 

3.       Only run the CTS_Data_Rebuild_Indexes.sql script for the proper version of the CTS_Data database you have installed. Please contact Linko Support with any questions.

 

 

Click the New Query button in your Management Studio main view.

 

 

 

Paste the script above into the New Query window.  Click the Execute button.