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