MS-SQL / DBCC CHECKDB

DBCC CHECKDB ('DB_NAME') WITH NO_INFOMSGS;

ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DBCC CHECKDB ('DB_NAME', REPAIR_REBUILD)

ALTER DATABASE DB_NAME SET MULTI_USER;

Syntax

DBCC CHECKDB     
    [ ( database_name | database_id | 0    
        [ , NOINDEX     
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]    
    ) ]    
    [ WITH     
        {    
            [ ALL_ERRORMSGS ]    
            [ , EXTENDED_LOGICAL_CHECKS ]     
            [ , NO_INFOMSGS ]    
            [ , TABLOCK ]    
            [ , ESTIMATEONLY ]    
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]    
            [ , MAXDOP  = number_of_processors ]    
        }    
    ]    
]    

https://msdn.microsoft.com/en-us/library/ms176064(d=printer).aspx

REPAIR_FASTMaintains syntax for backward compatibility only. No repair actions are performed.

REPAIR_REBUILD
Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.

REPAIR_REBUILD does not repair errors involving FILESTREAM data

ALL_ERRORMSGS

Displays all reported errors per object. All error messages are displayed by default. Specifying or omitting this option has no effect. Error messages are sorted by object ID, except for those messages generated from tempdb database.

InSQL Server Management Studio, the maximum number of error messages returned is 1000. When you specify ALL_ERRORMSGS, we recommend that you run the DBCC command by using the sqlcmd utility or by scheduling a SQL Server Agent job to run the command and direct the output to a file. Either of these methods will ensure that running the command once will report all error messages.

EXTENDED_LOGICAL_CHECKSIf the compatibility level is 100 (SQL Server 2008) or higher, performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.

NO_INFOMSGS

Suppresses all informational messages.

TABLOCK
Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. This includes a short-term exclusive (X) lock on the database. TABLOCK will cause DBCC CHECKDB to run faster on a database under heavy load, but decreases the concurrency available on the database while DBCC CHECKDB is running.

TABLOCK limits the checks that are performed; DBCC CHECKCATALOG is not run on the database, and Service Broker data is not validated.

ESTIMATEONLY
Displays the estimated amount of tempdb space that is required to run DBCC CHECKDB with all the other specified options. The actual database check is not performed.

PHYSICAL_ONLY
Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data.