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.


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.

Be the first to comment

Leave a Reply

Your email address will not be published.


*