Daily Archives: Sep 16, 2014


Rebuild indexes dynamically for all databases having fragmentation level more than defined level

Rebuild indexes dynamically for all databases

In this article we are going to learn how we can rebuild indexes for all the databases having fragmentation level more than defined level. Fragmentation level to rebuild the indexes may vary databases to database and In our example we are assuming it as 20%. Which can be suitable for most database servers.
We will also log the details of the indexes before rebuilding them and after rebuilding them in a table to get any report in near future.
And we will exclude all those indexes which have page counts less than 8, i.e. the table is too small and can fit in various different locations intentionally by DB engine.

Log Table Script:

CREATE TABLE MyDB.dbo.cmn_RebuildIndexesLog
(
 in_BatchNo INT,
 in_DBId INT,
 vc_DBName VARCHAR(256),
 vc_SchemaName VARCHAR(256),
 vc_TableName VARCHAR(256),
 vc_IndexName VARCHAR(256),
 num_AvgFragPercent NUMERIC(18,2),
 vc_TypeDesc CHAR(1) --Will store 'B' for before rebuild, 'A' for after rebuild
)

More