dbcc indexdefrag...Need Help

  • Last night i run DBCC INDEXDEFRAG and after few hours it complelety fill the log file, size of log file was about 30GB.

    Any idea that what happened and how to prevent from this in future.

    Executed as user: ...TE 01000] (Message 0) DBCC execution completed.

    If DBCC printed error messages, contact your system administrator.

    [SQLSTATE 01000] (Message 2528)

    DBCC INDEXDEFRAG (0,database,ADJ_RECORD_NUMBER_IDX1) [SQLSTATE 01000] (Message 0)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    [SQLSTATE 01000] (Message 2528) DBCC INDEXDEFRAG (0,database,CC_NUM_IDX1) [SQLSTATE 01000]

    (Message 0)

    DBCC execution completed.

    If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)

    DBCC INDEXDEFRAG (0,database,check_number) [SQLSTATE 01000] (Message 0) DBCC execution completed.

    If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)

    DBCC INDEXDEFRAG (0,database,dispatch_number) [SQLSTATE 01000] (Message 0) DBCC execution completed.

    If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]

    (Mes... The step failed.

    Thanks and Regards

    Arman

  • This kind of task always takes disk space, I mean a lot.

    You may shrink your database before starting your re-indexing.

    You may try to drop the index and re-create it. As a trade-off, the related tables will be locked while recreating indexes.

  • Sounds like you've got a seriously fragmented table there. How often do you backup your log? Backing it up more frequently will keep the log files trim during an index defrag.

    Sometimes, defragging an index uses up more log space than just doing a rebuild. And it can also take longer, depending on how fragmented the index is.

    I'd suggest you try rebuilding the index - bearing in mind that an index rebuild is not an online operation unless you have enterprise edition and you use the online option.

  • The DBCC INDEXDEFRAG command is fully logged. There are several things you could do. The first question, however, is what is the current recovery model of the database? From what you wrote, I'd guess it is in full recovery model.

    One thing you could do, is to run frequent transaction log backups during the time that the DBCC INDEXDEFRAG is running.

    The other is multistep. Change the database to the simple recovery model. Run your DBCC INDEXDFRAG. Change the recovery model back to full, and then most importantly, run a full backup of the database.

    Also, as you are using SQL Server 2005, I would look at starting to use the ALTER INDEX DDL statement instead of the DBCC INDEXDEFRAG as this will be removed from future versions of SQL Server.

    From BOL:

    Important:

    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead.

    😎

  • Thanks to All of You !!

    Just answer your questions.

    This is sql server 2000/sp4.The database is about 40GB. I am taking transaction log after every 2 hrs and after every 10 mins while the dbcc indexdefrag runs.

    My database is in full Recovery mode but when the job run it changes into Bulk.

    Thanks

  • Per BOL, DBCC INDEXDEFRAG is fully logged, so changing to BULK LOGGED is still the same as FULL RECOVERY for this operation.

    You may need to run the transaction logs more frequently while running the DBCC. Beyond that, don't know what else to tell you at this point.

    😎

  • In my memory, SQL 2005 does not log detail in the indexing, but the command.

    The problem is that the indexing takes space in its process. Since existing indexes take space, defragging indexes take space, I would like to recommend to drop rebuild index (not defrag indexes). Of course, we should shrink database after dropping indexes and before creating indexes.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply