Post reply

sql 2000 - Index Rebuild and Reorg - How to do ?

  • November 14, 2008 at 6:46 am

    #94338

    I would like to Rebuild and Reorganize my SQL Server 2000 table indexes, but dont know how to do it.

    In SQL 2005 and SQL 2008, you right click the index, under table name, but that is not an option in SQL 2000.

    Not sure what to do.

    Thanks

  • November 14, 2008 at 7:19 am

    #898566

    REBUILD LIKE THIS:

    DBCC DBREINDEX(tblName,'Index Names ',Fill Factor)

    If you want to Reindex all the tables and on all the indexes use this:

    exec sp_MSForEachTable '

    print ''?''

    DBCC DBREINDEX(''?'')

    print ''-----------------------------------------------------------------''

    '

    Fill Factor: Ideally it should be 100, which you need not specify, but if you table is constantly updated with DML's then specify a lower value like 80% to avoid page splits.

    Now you can defragment indexes with this statement below

    DBCC INDEXDEFRAG (DB Name,tableName, IndexName)

    GO

    To decide if you need to Defrag or ReIndex, use

    DBCC SHOWCONTIG(tblName)

    If the logical scan fragmentation is greater than 30% then Rebuild indexes, if it is between 10 to 30% then reorganize.

    Please be advised the Rebuild indexes locks the data in the table and should be done when there is no user activity. If rolled back it will revert back to the state you started whereas IndexDefrag commits the changes to the moment it was stopped.

    Thanks!!

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • November 14, 2008 at 7:44 am

    #898592

    Thanks !

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply