DBREINDEX and INDEXDEFRAG example

If you have to optimize a lot of tables and indexes. You could create a SQL script, which then could be scheduled.

Example a script that is starting with doing a reindex and the continues with indexdefrag.

 sql |  copy code |? 
CREATE   proc DB_Maint AS
 
set nocount on
 
DBCC DBReIndex ([$Post Code], [$1])
DBCC DBReIndex ([$Customer], [$1])
commit
 
RAISERROR ('Reindex done', 16, 1 ) with log, nowait
 
DBCC IndexDefrag (0, [$Customer], [$Customer$0])
DBCC IndexDefrag (0, [$Customer],  [$1])
DBCC IndexDefrag (0, [$Customer],  [$2])
 
commit
RAISERROR ('IndexDefrag Customer done', 16, 1 ) with log, nowait
 
commit
RAISERROR ('IndexDefrag Basis done', 16, 1 ) with log, nowait
 
GO

With the use of the RAISERROR command I am getting some log entries, and thereby I can always see how far the script is in its execution.

You can leave a response, or trackback from your own site.

Leave a Reply


eight × 2 =