Quick list of DBCC (Database Console Commands) in SQL Server
Database consistency check is a part of one of the important duty of DBA. I used to restore my each and every full database backup in one of the development server and execute DBCC command in that newly restored database. Once DBCC stops execution, I suppose to get an email with the status of this DBCC execution.
I have done automated process for all these steps, I will give more details about how to practically implement this process sometime later but today I wanted to show the list of some DBCC commands which are my favorite to check consistency of database.
Some of the DBCC commands given in following list may affect performance of SQL Server until it gets completed. If your database is large then some of the commands may take few hours for complete execution so be caution before running those commands in live environment, especially in business hours. As I have explained above, I used to restore my database backup somewhere and used to run DBCC command, not directly on production database, which is recommended method.
Anyway, let us have a look at different DBCC commands for different purpose.
Note: don’t run all commands together. Each DBCC command had different purpose and it is recommended to run each one of them separately and see behavior.
–CHECK THE DATABASE ALLOCATION and PAGE STRUCTURE integrity
–If running CHECKDB, no need to run CHECKALLOC separately
— Check the AdventureWorks2012 database without
–nonclustered indexes and extended logical checks.
DBCC CHECKDB (AdventureWorks2012, NOINDEX) WITH EXTENDED_LOGICAL_CHECKS;
–Extended Logical Checks and Physical Only cannot be used together
DBCC CHECKDB (AdventureWorks2012, NOINDEX) WITH PHYSICAL_ONLY;
–Checks the consistency of disk space allocation structures for a specified database.
–In our case, it is AdventureWorks2012
–Just an estimation and no elaborated messages
DBCC CHECKALLOC WITH ESTIMATEONLY,NO_INFOMSGS
–Following command check only PRIMARY filegroup
–check specific filegroup and integrity of the physicalstructure of the page
DBCC CHECKFILEGROUP (1, NOINDEX) WITH PHYSICAL_ONLY;
–Check data integirty for tables with different options for same table.
–execute each of the following three command separately and see the difference
DBCC CHECKTABLE (‘sales.SalesOrderDetail’) WITH ALL_ERRORMSGS
DBCC CHECKTABLE (‘sales.SalesOrderDetail’) WITH ESTIMATEONLY
DBCC CHECKTABLE (‘sales.SalesOrderDetail’, 1) WITH PHYSICAL_ONLY
–Check for constraints violation on specified table or indexed view
DBCC CHECKCONSTRAINTS (‘sales.SalesOrderDetail’)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.