SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Repairing with DBCC CHECKDB


Repairing with DBCC CHECKDB

Author
Message
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14256 Visits: 12197
Comments posted to this topic are about the item Repairing with DBCC CHECKDB

Tom

ma-516002
ma-516002
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2503 Visits: 321
Is the "no repair" answer correct?

http://msdn.microsoft.com/en-us/library/aa258278%28SQL.80%29.aspx

says:

REPAIR_FAST 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.
ma-516002
ma-516002
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2503 Visits: 321
Ah, I see, looked at the wrong SQL-Server version.
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3698 Visits: 4408
This is a good idea for QOD, unfortunately the QOD itself was ruined by a syntax error. The REPAIR_FAST argument cannot be used without a database name or ID.

http://msdn.microsoft.com/en-us/library/ms176064.aspx
DBCC CHECKDB (Transact-SQL)
Syntax

DBCC CHECKDB 
[
[ ( database_name | database_id | 0
[ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
) ]
[ WITH
{
[ ALL_ERRORMSGS ]
...
}
]
]



When you try to run the command "DBCC CHECKDB , REPAIR_FAST", you get the following error: "Msg 102, Level 15, State 1, Line 1. Incorrect syntax near ','."

The correct syntax is something like "DBCC CHECKDB (AdventureWorks, REPAIR_FAST)" or "DBCC CHECKDB (5, REPAIR_FAST)".

And the correct answer to the QOD is "returns an error message - you have to specify a database".
Bex
Bex
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1293 Visits: 1710
Got me! Good one, thanks!

Bex
M&M
M&M
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3535 Visits: 3906
There is also a forum discussion where Paul Randal has some comments.
Check this LINK.

M&M
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8839 Visits: 7281
Interesting question, thanks Tom.

Haven't used this option since SQL2000.
I presume this will eventually become deprecated.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27255 Visits: 13268
When I execute the command given in the question, I get the following error:

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


That's because you need to specify the database name in order to use the Repair_Fast option. So I choose answer 1, which was - to my surprise - incorrect.

edit: I see vk-kirov has already said the same thing. Whoopsie.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Michael Riemer
Michael Riemer
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2837 Visits: 638
Interesting question and have learnt something today, however I have to agree that the answer is actually wrong, as if you want to add any options to the DBCC command you have to specify the database name - and that in ().

So i would say that the answer should be the Error due to database name being required.
Even adding the database name and once you get the command to actually run, you still get the error that "Repair statement not processed. Database needs to be in single user mode."

If you have that, then you will get the actual answer that has been specified!

I definitely feel we shoudl get a point for the first error at any rate!
Michael G
Michael G
SSC Veteran
SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)

Group: General Forum Members
Points: 298 Visits: 154
I get: " Incorrect syntax near ','
You have to specify a db name.

Edit:
Heheheh - I see several others have latched onto the incorrect correct answer conundrum too. Mgilchrist = bad :-D

Michael Gilchrist
Database Specialist
There are 10 types of people in the world, those who understand binary and those that don't. :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search