DBCC CHECKDB is somewhat famous for DBA to run in case of database corruptions.
This is the syntax for it.
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.
Keep sending your comments.!!!
LinkedIn Group: SQL Server DBA
ReplyDeleteBut, 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
@ Dinesh
DeleteYes 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.
I am getting below error while executing
ReplyDeleteMsg 102, Level 15, State 1, Line 2
Incorrect syntax near '
[ ( DATABASE_NAME | DATABASE_ID | 0
[ , NOINDEX
| , {REPAIR_ALLOW_DATA_LOSS |REPAIR_FAST REPAIR_REBULID } '.