DBCC CheckDB

DBCC CHECKDB is used to check the physical integrity of the entire database. DBCC CHECKDB is used to detect corruption in the database, and should be run regularly.  This is accomplished by running several of the other DBCC commands, then doing some extra verification.

• Runs DBCC CHECKALLOC.
• Runs for every table DBCC CHECKTABLE.
• Runs DBCC CHECKCATALOG.
• Validates the Service Broker data in the database.
• Validates indexed views.

You may or may not have heard of DBCC (database console commands) statements. These statements are used to perform different operations in your database and can be broken down into four categories: Maintenance, Miscellaneous, Informational, and Validation.

DBCC CHECKDB takes some time for large databases.

DBCC CHECKDB warns you of corruption so that you can fix it before it gets too bad.

DBCC CHECKDB ('MyDB')

DBCC CHECKDB ('MyDB') WITH NO_INFOMSGS

-- Low overhead check of the physical consistency of the database
DBCC CHECKDB('MyDB') WITH PHYSICAL_ONLY

Database with no errors…

CHECKDB found 0 allocation errors and 0 consistency errors in database 'xxxxx'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Sources:

https://www.mssqltips.com/sqlservertip/4381/sql-server-dbcc-checkdb-overview/