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.
Leave a Reply