Is SQL Job Required to Maintain Indexes for TFS databases

  • Article

The recommendation from the Product Group is to not run a separate index maintenance job as the Optimize Databases TFS job will handle updating of indexes for TFS related databases (Config/Collection DBs). The stored procedure prc_OptimizeDatabase goes through all the indices (including clustered indices) and rebuilds them online if they are more than 20% fragmented. Some of the indices are blacklisted (i.e. not rebuilt) – you can find these in tbl_ReindexExclusions.

From the comments in the prc_OptimizeDatabase stored procedure, it appears the tbl_content table is skipped in this job and it is the only entry in the tbl_ReindexExclusions table.

-- First we defragment all existing indices

-- Iterate through all tables and call ALTER INDEX

-- Exclude tbl_Content as it gets to be really large and does not need re-organization

This article explains some basic SQL admin tasks there is nothing different for TFS databases compared to other transactional databases.