Monday, June 18, 2012

REBUILD_FAST option in DBCC CHECKDB

DBCC CHECKDB is somewhat famous for DBA to run in case of database corruptions.

This is the syntax for it.

image

So incase of a corruption, you can have either of three parameters, REPAIR_FAST, REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS. Out of these parameters let us focus on REPAIR_FAST. This is the documentation for REPAIR_FAST in SQL Server 2000

Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.

However, do you know that this is no more with SQL Server 2005 onwards.

This is the documentation for the REPAIR_FAST.

Maintains syntax for backward compatibility only. No repair actions are performed. Hot smile

Keep sending your comments.!!!

4 comments:

  1. LinkedIn Group: SQL Server DBA

    But, this requires the DB to be in Single User Mode ( am i correct ? ) these days you cannot do this in many Production enviroments.
    Posted by Glen Joseph

    ReplyDelete
    Replies
    1. @ Dinesh
      Yes you are right, you can change a database status from multi user mode to single user mode by following query:

      "ALTER DATABASE yourDatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE". It is recommended to run DBCC Checkdb on the database at least once in a week to check the physical & logical consistency of database. If DBCC Checkdb reports any error message then the best solution is to restore from recent backup.

      If you don't have recent backup then use DBCC check with repair_allow_data_loss on the reported database but you may loss some of your data. If you don't want to lose any data then use any sql server database repair software.

      Delete
  2. sql database recovery tool damaged .mdf files after using unstable software, hard drive fails, system errors. Utility restores tables, procedures and etc. items. It is compatible with any software configuration of your PC.

    ReplyDelete
  3. I am getting below error while executing

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near '

    [ ( DATABASE_NAME | DATABASE_ID | 0
    [ , NOINDEX
    | , {REPAIR_ALLOW_DATA_LOSS |REPAIR_FAST REPAIR_REBULID } '.

    ReplyDelete