Has anyone played around automating DBCC INDEXDEFRAG? I am in the process of setting up a job that will do a dbcc showcontig on all our databases every night, then based on the logicalfrag number, it will pick the top 3 fragmented indexes per database and run the dbcc indexdefrag command. I am having a couple of problems...
First, any ideas on how to capture the output of the dbcc indexdefrag? If i do something like
INSERT INTO #temp
it wont run because the defrag cant run in a user transaction which is created due to the insert statement.
Anyway, I need to find out if the defrag completed successfully.
Second, when running the defrag within an exec statement, as above, It only seems to work part of the time. I look at the result window, and for most of the indexes it says it completed it, yet 0 pages were moved and 0 pages removed. However, if I run the dbcc indexdefrag statement on its own, it seems to make changes... Any thoughts? anyone else find problems with the defrag?
Open the BOL and do a search on the "DBCC SHOWCONTIG" topic. In the section E. There is a script that you can cut & paste, then schedule a job and run till your hear content (with whatever level of fragmentation that you wanted to defrag, by changing the "SELECT @maxfrag = xx " at the top of the script. I cut &paste it here for anyone who doesn't have the BOL handy:
E. Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
This example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold.
/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0
-- Declare cursor
DECLARE tables CURSOR FOR
WHERE TABLE_TYPE = 'BASE TABLE'
-- Loop through all the tables in the database
WHILE @@FETCH_STATUS = 0
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
-- Close and deallocate the cursor
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
-- loop through the indexes
INTO @tablename, @objectid, @indexid, @frag