As a SQL Server DBA, it is very command to use various DBCC commands to retrieve data required to understand problems or to get the certain status information or details. DBCC stands for Database Console Commands. There are many documented and undocumented DBCC commands in SQL Server which are very useful for DBAs and are used in different usage like to retrieve usage information, to run maintenance tasks, To validate databases, and other Miscellaneous purposes.
Below are some of the DBCC commands related to Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
DBCC CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database. DBCC CHECKALLOC checks the allocation of all pages in the database, regardless of the type of page or type of object to which they belong. It also validates the various internal structures that are used to keep track of these pages and the relationships between them. If NO_INFOMSGS is not specified, DBCC CHECKALLOC collects space usage information for all objects in the database.
DBCC CHECKCATALOG – Checks for catalog consistency within the specified database. The database must be online. After the DBCC CATALOG command finishes, a message is written to the SQL Server error log. If the DBCC command successfully executes, the message indicates a successful completion and the amount of time that the command ran. If the DBCC command stops before completing the check because of an error, the message indicates the command was terminated, a state value, and the amount of time the command ran.
DBCC CHECKCONSTRAINTS – Checks the integrity of a specified constraint or all constraints on a specified table in the current database. DBCC CHECKCONSTRAINTS constructs and executes a query for all FOREIGN KEY constraints and CHECK constraints on a table.
DBCC CHECKDB – Checks the logical and physical integrity of all the objects in the specified database by performing the following operations. DBCC CHECKDB is supported on databases that contain memory-optimized tables but validation only occurs on disk-based tables. However, as part of database backup and recovery, a CHECKSUM validation is done for files in memory-optimized filegroups.
Since DBCC repair options are not available for memory-optimized tables, you must back up your databases regularly and test the backups. If data integrity issues occur in a memory-optimized table, you must restore from the last known good backup. Please note that DBCC CHECKDB does not examine disabled indexes. DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks. This prevents blocking and concurrency problems when these commands are executed.
DBCC CHECKFILEGROUP – Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database. DBCC CHECKFILEGROUP and DBCC CHECKDB are similar DBCC commands. The main difference is that DBCC CHECKFILEGROUP is limited to the single specified filegroup and required tables.
DBCC CHECKIDENT – Checks the current identity value for the specified table in SQL Server and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column. The specific corrections made to the current identity value depend on the parameter specifications.
DBCC CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.