• IGNORE THIS ENTRY SEE CORRECTION BY SQLkiwi Posted Today @ 4:23 AM

    Here we go again

    Try it as:

    DBCC INDEXDEFRAG (Master,"dbo.spt_values",ix2_spt_values_nu_nc)

    DBCC INDEXDEFRAG (AdventureWorks_regular,"cdc.ddl_history",ddl_history_clustered_idx)-- a system table in the AdventureWorks_regular DB

    Your reference states

    DBCC INDEXDEFRAG cannot be used to defragment the following indexes:

    A disabled index.

    An index with page locking set to OFF.

    A spatial index.

    DBCC INDEXDEFRAG is not supported for use on system tables.

    It may be depreciated in the future and it may not be supported on system tables ...BUT IT WORKS NOW in both the MASTER DB and a system table in the AdventureWorks_regular data bases using SQL 2008 or SQL 2005

    My testing:

    Using 2005

    DBCC INDEXDEFRAG (Master,"dbo.spt_values",ix2_spt_values_nu_nc)--Original system table

    Pages Scanned Pages Moved Pages Removed

    14 6 4

    Using 2008

    DBCC INDEXDEFRAG (Master,"dbo.spt_values",spt_valuesclust)--System table

    Pages ScannedPages MovedPages Removed

    18 8 3

    Using 2008

    DBCC INDEXDEFRAG (AdventureWorks_regular,"cdc.ddl_history",ddl_history_clustered_idx)-- a system table in AdventureWorks_regular

    Pages ScannedPages MovedPages Removed

    14 6 4

    IGNORE THIS ENTRY SEE CORRECTION BY SQLkiwi Posted Today @ 4:23 AM

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]