Share via

Creating a job to re-index all user databases in an instance of SQL Server 2005/2008 Enterprise Edition

  • Article

Here is a script that will create a job to re-index each user database in an instance that is not in read-only mode. The script uses a cursor to identify the available databases and creates a job step with a script to perform online reindexing on that database. The script is derived from an MSDN example script. It uses the dm_db_index_physical_stats function with the "limited" parameter to determine which indexes require defragmentation or rebuilding. Fragmentation thresholds and the minimum index size can be set. It ignores hypothetical and disabled indexes. Thanks to Geoff Hiten of the Atlanta.mdf user group who published changes to the original sample script to handle BLOB columns.

 The intended purpose of this script is to reduce the amount of time a DBA has to spend handling tasks such as this. The script implements push-button functionality to simplify the task of creating and updating index maintenance processes on a server.

I won't go into all the details about re-indexing. You can find that in MSDN and Books Online.

Posting updated January 9, 2009:

The DBID function does not work with a database running in SQL Server 2000 (8.0) compatible mode on 2005 or 2008. The solution is to explicitly pass in the database id value to the dm_db_index_physical_stats function. I am not going to update this script. If you want to see how it's done, please see my earlier post: Finding Fragmented Indexes in a Database Running in SQL Server 2000 Compatibility Mode on 2005.

CreateReindexJobSQLEnterprise_BlogPost.sql

Comments

  • Anonymous
    January 05, 2009
    The comment has been removed

  • Anonymous
    January 07, 2009
    Hi Tom, Classic over-coding from a Microsoft/IBM type. Just the query to get these tables in a fragmented state runs forever. I would never use this because it is just too badly written. Here's the simple way to prepare to reindex databases. I built it for 2000 but it works fine on 2005. --Build the list of Tables and indexes along with the DBCC string DECLARE  @TsAndIs TABLE (T_Name VARCHAR(500) NULL, I_Name VARCHAR(250) NULL) INSERT INTO @TsAndIs (T_name, I_name) SELECT 'DBCC INDEXDEFRAG (0,' + CAST(sysobjects.id AS VARCHAR) + ',' + sysindexes.name + ')',sysindexes.name FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id WHERE sysindexes.indid > 0 AND sysindexes.indid < 255 AND (sysindexes.status & 64)=0 AND sysobjects.xtype NOT IN ('S', 'PK', 'V', 'P','F','D') AND sysobjects.name <> 'dtproperties' SELECT T_name from @TsAndIs /*Paste the output into a job; break the job into sections because you can't have that many strings in a single job step in 2000. OH! and it runs in about a second. Note: when the programmers and vendors create keyword index names, you have to enclose the results in brackets. I wish Microsoft had experienced people doing stuff instead of this kind of overcoding. */

  • Anonymous
    January 07, 2009
    The comment has been removed

  • Anonymous
    January 07, 2009
    Please note that the DBCC INDEXDEFRAG command will be deprecated in the next version of SQL Server. ALTER INDEX is the preferred method to maintain indexes.

  • Anonymous
    January 08, 2009
    It looks like when compatibility level is set to SQL 2000 the following code brakes: SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') with error Incorrect syntax near '('. Thanks.

  • Anonymous
    January 20, 2009
    The comment has been removed

  • Anonymous
    January 21, 2009
    Although you can pass a NULL or zero for the first parameter of the dm_db_index_physical_stats function to get the stats for all databases, that is out of scope for the query in the script. the query is trying to get the stats for the current database only. Please understand that this query generates separate job steps for each database. Therefore, when we're executing the job step, we only want stats for that particular database. The solution is to use the DB_NAME function. You'll need to declare a variable and use this SQL statement to get the current database id: SELECT @yourvariable = DB_ID(DB_NAME()) Then pass that variable into the function instead of the DB_ID.

  • Anonymous
    April 10, 2009
    Where can I get the "There is a companion update statistics script and job that I have not posted that completed in approximately 45 minutes on the same system." Thks!

  • Anonymous
    April 10, 2009
    The Update Statistics job I referenced is not published. It is a slightly modified version of the code in the system job to update statistics. I decided not to publish it since it can easily  be derived from the system stored procedure.

  • Anonymous
    August 01, 2011
    I receive the following error when I click on the 'file attachment' link: Sorry, there was a problem with your last request! Either the site is offline or an unhandled error occurred. We apologize and have logged the error. Please try your request again or if you know who your site administrator is let them know too. How may I download or view the script?

  • Anonymous
    August 02, 2011
    The "file attachment" in the older part of the post was confusing. I've deleted it. The correct script is available at the end of the post.