Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    63

    Unanswered: Dbcc Indexdefrag ...

    Howdy,

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

    SET @s_str = 'DBCC INDEXDEFRAG (' + @dbname + ', ' + @tablename + ',' + @indxname + ')'

    INSERT INTO #temp
    EXEC (@s_str)
    ...
    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?

    TIA

  2. #2
    Join Date
    Jun 2003
    Posts
    31
    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
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'

    -- Create the table
    CREATE TABLE #fraglist (
    ObjectName CHAR (255),
    ObjectId INT,
    IndexName CHAR (255),
    IndexId INT,
    Lvl INT,
    CountPages INT,
    CountRows INT,
    MinRecSize INT,
    MaxRecSize INT,
    AvgRecSize INT,
    ForRecCount INT,
    Extents INT,
    ExtentSwitches INT,
    AvgFreeBytes INT,
    AvgPageDensity INT,
    ScanDensity DECIMAL,
    BestCount INT,
    ActualCount INT,
    LogicalFrag DECIMAL,
    ExtentFrag DECIMAL)

    -- Open the cursor
    OPEN tables

    -- Loop through all the tables in the database
    FETCH NEXT
    FROM tables
    INTO @tablename

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Do the showcontig of all indexes of the table
    INSERT INTO #fraglist
    EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
    WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
    FETCH NEXT
    FROM tables
    INTO @tablename
    END

    -- Close and deallocate the cursor
    CLOSE tables
    DEALLOCATE tables

    -- Declare cursor for list of indexes to be defragged
    DECLARE indexes CURSOR FOR
    SELECT ObjectName, ObjectId, IndexId, LogicalFrag
    FROM #fraglist
    WHERE LogicalFrag >= @maxfrag
    AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

    -- Open the cursor
    OPEN indexes

    -- loop through the indexes
    FETCH NEXT
    FROM indexes
    INTO @tablename, @objectid, @indexid, @frag

    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
    ' + RTRIM(@indexid) + ') - fragmentation currently '
    + RTRIM(CONVERT(varchar(15),@frag)) + '%'
    SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
    ' + RTRIM(@indexid) + ')'
    EXEC (@execstr)

    FETCH NEXT
    FROM indexes
    INTO @tablename, @objectid, @indexid, @frag
    END

    -- Close and deallocate the cursor
    CLOSE indexes
    DEALLOCATE indexes

    -- Delete the temporary table
    DROP TABLE #fraglist
    GO


    -- Good luck :-)

    SVT

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •