Search

Option to check constraints in DBCC CHECKDB by Ola Hallengren

Resolved
as Won't Fix Help for as Won't Fix

56
1
Sign in
 to vote
Type: Suggestion
ID: 508837
Opened: 11/6/2009 12:58:41 PM
Access Restriction: Public
0
Workaround(s)
I would like to have an option in DBCC CHECKDB to check all constraints in the database.

If this is successful it should also set all constraints in the database to trusted.
Details (expand)
Product Language
English

Category

SQL Engine

Proposed Solution

DBCC CHECKDB ([AdventureWorks]) WITH CHECKCONSTRAINTS;

Benefits

Improved Reliability
Improved Administration
Improved Performance

Other Benefits

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by Paul White NZ on 1/16/2012 at 10:22 AM
Awesome response and detailed reasoning for a Connect item with 54 upvotes :-/
Posted by Microsoft on 1/3/2012 at 11:52 AM
Thanks for your suggestion. We have no plans to add any additional options to CHECK* commands in any future release. Scripting your CHECKCONSTRAINTS calls is the best alternative.
Posted by Microsoft on 1/3/2012 at 11:52 AM
Thanks for your suggestion. We have no plans to add any additional options to CHECK* commands in any future release. Scripting your CHECKCONSTRAINTS calls is the best alternative.
Posted by Ola Hallengren on 11/17/2009 at 2:29 PM
Including it in EXTENDED_LOGICAL_CHECKS would be fine. Thanks for considering it.

(Could you also have a look at another Connect suggestion that I have about CHECKDB. It has id 468694.)
Posted by Microsoft on 11/17/2009 at 2:20 PM
Thanks for the quick response! I agree that running one command to do a full check of the database is easier than running two. I'll classify this as something we'll look at in planning our next release. One option we would consider is including constraint checks in the WITH EXTENDED_LOGICAL_CHECKS option.

Thanks again for the feedback!
David Cohen
SQL Server Program Management
Posted by Ola Hallengren on 11/17/2009 at 1:57 PM
Thanks for the response. Yes, the workaround is to run CHECKCONSTRAINTS on every constraint in the database using a script, but I think that it would be useful to have it in CHECKDB as it is more easy to use if you want to do a complete check of your database.

(You could argue that DBCC CHECKDB is not needed at all. You could just run CHECKALLOC and CHECKCATALOG on the database and CHECKTABLE on every table in the database.)
Posted by Microsoft on 11/17/2009 at 1:47 PM
(resending, in case original message was lost)

Hello,

Thanks for your feedback. On the surface, it seems as though the obvious (and not too painful?) workaround would be to run CHECKCONSTRAINTS (either manually or in a script) either before or after running CHECKDB. Is there another scenario you're thinking of where keeping CHECKDB and CHECKCONSTRAINTS separate is especially painful?

Thanks,
David Cohen
SQL Server Program Management
Posted by Microsoft on 11/17/2009 at 1:38 PM
Hello,

Thanks for the feedback. On the surface, it seems as though the obvious workaround is to run CHECKCONSTRAINTS (either in a script, or manually) before or after you run CHECKDB. Is there a different scenario that makes CHECKDB and CHECKCONSTRAINTS being separate especially painful?

Thanks,
David Cohen
SQL Server Program Management
Sign in to post a workaround.
© 2012 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement