Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Repairing with DBCC CHECKDB Expand / Collapse
Author
Message
Posted Thursday, June 14, 2012 10:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:12 AM
Points: 7,791, Visits: 9,545
Comments posted to this topic are about the item Repairing with DBCC CHECKDB

Tom
Post #1316289
Posted Friday, June 15, 2012 12:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:52 AM
Points: 2,256, Visits: 308
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.
Post #1316319
Posted Friday, June 15, 2012 12:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:52 AM
Points: 2,256, Visits: 308
Ah, I see, looked at the wrong SQL-Server version.
Post #1316320
Posted Friday, June 15, 2012 1:01 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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".
Post #1316339
Posted Friday, June 15, 2012 1:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:56 AM
Points: 1,188, Visits: 1,551
Got me! Good one, thanks!

Bex
Post #1316351
Posted Friday, June 15, 2012 2:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:12 PM
Points: 2,278, Visits: 3,798
There is also a forum discussion where Paul Randal has some comments.
Check this LINK.


Mohammed Moinudheen
Post #1316379
Posted Friday, June 15, 2012 2:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 4,013, Visits: 5,307
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”
Post #1316381
Posted Friday, June 15, 2012 2:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:37 AM
Points: 13,250, Visits: 11,028
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1316386
Posted Friday, June 15, 2012 2:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 17, 2014 1:15 AM
Points: 1,805, Visits: 496
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!
Post #1316392
Posted Friday, June 15, 2012 4:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 7, 2014 5:55 AM
Points: 196, Visits: 135
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


Michael Gilchrist
Database Specialist
http://www.michael-gilchrist.com
There are 10 types of people in the world, those who understand binary and those that don't.
Post #1316478
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse