Defrag All SQL Server Indexes

Update: Try here for a script to run DBCC DBREINDEX on all indexes on all tables in a database:

Here's the one line to do a DBREINDEX on all indexes in a databases.  It uses an unofficial SQL Server function.  Also, remember to do this during down time!  This LOCKS tables. 

USE xxx;
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)";

Sometimes, you'll want to SHRINK the DB after the DBREINDEX:


Loop all user tables in SQL Server, optimize all indexes, etc.

Some other good TSQL scripts and stuff:

There is a great script in BOL for 2000, that will do the online defrag of only indexes which are above a threshold in fragmentation.


"DBCC INDEXDEFRAG does not help to untangle indexes that have become interleaved within a data file. Likewise, DBCC INDEXDEFRAG does not correct extent fragmentation on indexes. Interleaving occurs when index extents (a group of eight index pages) for an index are not completely contiguous within the data file, leaving extents from one or more indexes intermingled in the file. Interleaving can occur even when there is no logical fragmentation, because all index pages are not necessarily contiguous, even when logical ordering matches physical ordering."


Reindex one table: DBCC DBREINDEX ('HumanResources.Employee', ' ', 90);


"DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table." -- I think this is referred to as "Rebuild Index" in newer SQL Server Maintenance Plan Wizards.

tags: script, optimize, reindex, mssql, defrag, defragment, logical, extent, frags, fragment, fragments, rebuild, index, compact, shrink

Related Scribbles:
  • SQL Server

  • ID: 775
    Date Updated:
    2016-07-28 10:52:35
    Date Created:
    2006-06-06 14:23:28


     >> Leonard Chan's Homepage  >> Scribble Web  >> Defrag All SQL Server Indexes is hosted by Perceptus Solutions Inc.