SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tom Thomson / Repairing with DBCC CHECKDB / Latest Posts

RE: Repairing with DBCC CHECKDB

Hugo Kornelis — Sat, 06 Oct 2012 10:46:00 GMT

[quote][b]kapil190588 (10/6/2012)[/b][hr]hi,Can anyone tell me How to check whether the database is in single user mode or multi user mode?[/quote][code="sql"]SELECT user_access, user_access_descFROM sys.databasesWHERE name = 'YourDatabase';[/code]

RE: Repairing with DBCC CHECKDB

kapil_kk — Sat, 06 Oct 2012 07:40:07 GMT

hi,Can anyone tell me How to check whether the database is in single user mode or multi user mode?

RE: Repairing with DBCC CHECKDB

Neha05 — Thu, 09 Aug 2012 08:33:12 GMT

Good question!

RE: Repairing with DBCC CHECKDB

L' Eomot Inversé — Wed, 20 Jun 2012 14:45:55 GMT

[quote][b]I knew that repair_fast no longer worked, but guessed wrong that it would fail with an error rather than just being ignored. I hate it when MS do this - backward compatibility is all very well but in some cases can lead you to think that an action has completed successfully rather than doing nothing! Am I the only one who'd rather start getting error messages so I knew I had to fix my code?[/quote] You're certainly not the only one. A warning message might be OK instead of an error, but quietly doing nothing is irritating.edit:spelling!

RE: Repairing with DBCC CHECKDB

Toreador — Tue, 19 Jun 2012 02:14:34 GMT

Am I allowed to complain that I got an error because I have no such database as Adventureworks? ;-)I knew that repair_fast no longer worked, but guessed wrong that it would fail with an error rather than just being ignored. I hate it when MS do this - backward compatibility is all very well but in some cases can lead you to think that an action has completed successfully rather than doing nothing! Am I the only one who'd rather start getting error messages so I knew I had to fix my code?

RE: Repairing with DBCC CHECKDB

zymos — Mon, 18 Jun 2012 11:29:15 GMT

Easy one and no repairs would be done in this case.Thanks.

RE: Repairing with DBCC CHECKDB

SQLRNNR — Mon, 18 Jun 2012 11:18:27 GMT

Thanks Tom.

RE: Repairing with DBCC CHECKDB

cfradenburg — Fri, 15 Jun 2012 11:01:57 GMT

Good question. I knew it wasn't valid any more but had forgotten that it wouldn't error.

RE: Repairing with DBCC CHECKDB

OzYbOi d(-_-)b — Fri, 15 Jun 2012 09:34:57 GMT

Thanks for the question - cheers

RE: Repairing with DBCC CHECKDB

Andre Ranieri — Fri, 15 Jun 2012 09:28:14 GMT

As a point of intellectual curiosity, when I run this against my sandbox database (SQL 2008 R2) I get an error that the database needs to be in single user mode.Thanks again for everything. I start every morning with the SSC QoTD, and it has taught me an awful lot.Andre Ranieri

RE: Repairing with DBCC CHECKDB

Steve Jones - SSC Editor — Fri, 15 Jun 2012 09:21:48 GMT

The typo has been corrected and points awarded back.My apologies for the typo.

RE: Repairing with DBCC CHECKDB

mtassin — Fri, 15 Jun 2012 08:30:36 GMT

I got this right because my copy of Local Transact SQL Help says as does BOL[quote]' database_name ' | database_id | 0Is the name or ID of the database for which to run integrity checks. If not specified, or if 0 is specified, the current database is used. Database names must comply with the rules for identifiers.REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILDSpecifies that DBCC CHECKDB repair the found errors. The specified database must be in single-user mode to use one of the following repair options.[/quote]I read it such that if you don't specify a database it will default to the current one. I guess not with the REPAIR commands?Originally I thought it was an error... but reading that it seems that it should be right... it's not... but I think it should be.

RE: Repairing with DBCC CHECKDB

David Smerchek — Fri, 15 Jun 2012 08:29:00 GMT

in SQL 2012 running this you will get a syntex error near ","

RE: Repairing with DBCC CHECKDB

Andre Ranieri — Fri, 15 Jun 2012 07:56:31 GMT

I got this one wrong (or right) because my answer assumes that DBCC CheckDB requires a database name, and in parentheses. At any rate, I didn't get the point but it was nice to brush up on some SQL history.Thanks.

RE: Repairing with DBCC CHECKDB

EL Jerry — Fri, 15 Jun 2012 07:43:30 GMT

Got me on this one because I read an old version of BOL. I knew there was a syntax error but I focused only on the words and not the punctuation.I should pay attention to versions in later QOTDs.:-DHappy weekend everyone!!!"El" Jerry.

RE: Repairing with DBCC CHECKDB

Mark D Powell — Fri, 15 Jun 2012 07:18:04 GMT

The question specifically states 2008+ and on 2008 the syntax is invalid. The first answer is the correct answer, not the answer you indicate.

RE: Repairing with DBCC CHECKDB

Robert Davis — Fri, 15 Jun 2012 07:04:11 GMT

Since the syntax error due to the comma wasn't one of the choices, I assumed the comma was a typo and answered the question as if it wasn't there. Got it right.

RE: Repairing with DBCC CHECKDB

Steve Cullen — Fri, 15 Jun 2012 06:58:37 GMT

QOD <<FAIL>>

RE: Repairing with DBCC CHECKDB

sestell1 — Fri, 15 Jun 2012 06:45:23 GMT

Ugh, I didn't realize that had been depreciated. Thanks for the question Tom!

RE: Repairing with DBCC CHECKDB

Mike Hays — Fri, 15 Jun 2012 06:29:48 GMT

Agree, got the same result as the post above above about syntax error. Is there any quality control on these questions?!?!

RE: Repairing with DBCC CHECKDB

L' Eomot Inversé — Fri, 15 Jun 2012 06:03:07 GMT

Apologies to everyone for the silly syntax error.I'll ask Steve to correct the question.

RE: Repairing with DBCC CHECKDB

Michael G — Fri, 15 Jun 2012 04:35:40 GMT

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

RE: Repairing with DBCC CHECKDB

Michael Riemer — Fri, 15 Jun 2012 02:49:49 GMT

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!

RE: Repairing with DBCC CHECKDB

Koen Verbeeck — Fri, 15 Jun 2012 02:42:04 GMT

When I execute the command given in the question, I get the following error:[quote]Msg 102, Level 15, State 1, Line 1Incorrect syntax near ','.[/quote]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.

RE: Repairing with DBCC CHECKDB

Stewart "Arturius" Campbell — Fri, 15 Jun 2012 02:29:38 GMT

Interesting question, thanks Tom.Haven't used this option since SQL2000.I presume this will eventually become deprecated.

RE: Repairing with DBCC CHECKDB

mohammed moinudheen — Fri, 15 Jun 2012 02:20:56 GMT

There is also a forum discussion where Paul Randal has some comments. Check this [url=http://www.sqlservercentral.com/Forums/Topic405557-266-1.aspx#bm409239]LINK[/url].

RE: Repairing with DBCC CHECKDB

Bex — Fri, 15 Jun 2012 01:20:59 GMT

Got me! Good one, thanks!Bex

RE: Repairing with DBCC CHECKDB

vk-kirov — Fri, 15 Jun 2012 01:01:32 GMT

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.[url]http://msdn.microsoft.com/en-us/library/ms176064.aspx[/url][quote]DBCC CHECKDB (Transact-SQL)Syntax[hr][code]DBCC CHECKDB [ [ ( database_name | database_id | 0 [ , NOINDEX | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ) ] [ WITH { [ ALL_ERRORMSGS ] ... } ]][/code][/quote]When you try to run the command "DBCC CHECKDB , REPAIR_FAST", you get the following error: "[color="red"]Msg 102, Level 15, State 1, Line 1. Incorrect syntax near ','.[/color]"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".

RE: Repairing with DBCC CHECKDB

ma-516002 — Fri, 15 Jun 2012 00:19:53 GMT

Ah, I see, looked at the wrong SQL-Server version.

RE: Repairing with DBCC CHECKDB

ma-516002 — Fri, 15 Jun 2012 00:18:31 GMT

Is the "no repair" answer correct?http://msdn.microsoft.com/en-us/library/aa258278%28SQL.80%29.aspxsays: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.

Repairing with DBCC CHECKDB

L' Eomot Inversé — Thu, 14 Jun 2012 22:22:56 GMT

Comments posted to this topic are about the item [B]Repairing with DBCC CHECKDB[/B]