Script to rebuild Indexes on a Helpdesk database.

Version 6

    The attached script will drop and re-create all the indexes on the Helpdesk/SupportCenter database.

     

    This script is escpecially useful if the customer is missing indexes for any reason or they are experiencing performance problems and all other common causes have been ruled out.


    This script has been designed to use the TRY can CATCH construct introduced in SQL 2005.  If the customers database is on a version of SQL server prior to SQL 2005, you will need to restore their database to an SQL 2005 or later environment, run this script against the database making sure you set the GenerateScriptForPreSQL2005 variable to 'TRUE'.  You can then copy the results and run the results script against the customers SQL 2000 or SQL 7 database.

     
    By default, the script will rebuild the primary indexes as unique clustered indexes to improve performance.  You can change the @CreateClusteredPrimaryIndex variable to 'FALSE' to have it recreate nonclustered indexes (replicate ootb indexes) if prefered.

     

    Please do not run this script on a live environment without a successful trial on a test environment.

    Always take a full database backup before running scripts on a live environment.

     

    If the script returns errors, this does not necessarily indicate a problem.  One example of this is if the customer has added a primary key to a table.  The index will fail to both drop the primary key and create the new index.  This is not an issue providing the primary key is based on the same columns as the primary index the script is trying to drop/create.  The primary indexes in Helpdesk/Supportcenter have an index name of 'Table0' e.g. CALL_HDW0

     

    Please read the additional notes contained within the script and familiarise yourself with it before running the script.

     

    If you have any questions regarding the execution of this script or any results or errors which are returned, please contact Support.