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

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.



Comments (11)

  1. Vadim says:

    Great script Tom – Thank you.

    Few things I’ve noticed:

    1. The select statement for DBNames_cursor will pick up OffLine databases. (so, i’ve added AND [state] = 0)

    2. You probably should mention that as written it will only work in Enterprise Edition:

    Msg 1712, Level 16, State 1, Line 1

    Online index operations can only be performed in Enterprise edition of SQL Server.

    3. For REORGANIZE I’m getting:

    Msg 155, Level 15, State 1, Line 1

    ‘ONLINE’ is not a recognized ALTER INDEX REORGANIZE option.


    ‘SORT_IN_TEMPDB’ is not a recognized ALTER INDEX REORGANIZE option.

    Thank you.

  2. Don Goodman says:

    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


    INSERT INTO @TsAndIs (T_name, I_name)

    SELECT ‘DBCC INDEXDEFRAG (0,’ + CAST( AS VARCHAR) + ‘,’ + + ‘)’, FROM sysobjects INNER JOIN sysindexes

    ON =

    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 <> ‘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.


  3. Don,

    Thank you for contributing a script that will run with SQL Server 2000.

    True, the dm_db_index_physical_stats function does take a while to run since it has to physically read the current index statistics. But the script makes use of that function to decide what indexes need to be rebuilt, reorganized or skipped because they are not fragmented. the intent is to increase the efficiency of the index maintenance operation.

    Yes, this script does have a lot of code. But the intention is to minimize the time required by a DBA to create or update an index maintenance job on a server. One click functionality requires additional code to implement but the time savings for a DBA justifies the extra lines of code.

    Here’s some real world stats:

    The job created by my script re-indexed 41 databases containing approximately 3TB of data in a high-volume SQL Server 2005 production system in two hours while maintaining system availability. There is a companion update statistics script and job that I have not posted that completed in approximately 45 minutes on the same system.

  4. 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.

  5. Vadim says:

    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 ‘(‘.


  6. Emilio says:

    Hey, big project.

    I tried and changed the DB_ID () for NULL for all databases

    and get the following error:

    Msg 2727, Level 11, State 1, Line 1

    Can not find index ‘clust.

    Any idea?


  7. 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.

  8. lawrencelozzano says:

    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!

  9. 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.

  10. djninow says:

    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?

  11. 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.