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.
USE AdventureWorks2012 GO --CHECK THE DATABASE ALLOCATION and PAGE STRUCTURE integrity --If running CHECKDB, no need to run CHECKALLOC separately DBCC CHECKDB; GO -- Check the AdventureWorks2012 database without --nonclustered indexes and extended logical checks. DBCC CHECKDB (AdventureWorks2012, NOINDEX) WITH EXTENDED_LOGICAL_CHECKS; GO --Extended Logical Checks and Physical Only cannot be used together DBCC CHECKDB (AdventureWorks2012, NOINDEX) WITH PHYSICAL_ONLY; GO --Checks the consistency of disk space allocation structures for a specified database. --In our case, it is AdventureWorks2012 DBCC CHECKALLOC; GO --Just an estimation and no elaborated messages DBCC CHECKALLOC WITH ESTIMATEONLY,NO_INFOMSGS --Following command check only PRIMARY filegroup DBCC CHECKFILEGROUP; GO --check specific filegroup and integrity of the physicalstructure of the page DBCC CHECKFILEGROUP (1, NOINDEX) WITH PHYSICAL_ONLY; GO --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 GO DBCC CHECKTABLE ('sales.SalesOrderDetail') WITH ESTIMATEONLY GO DBCC CHECKTABLE ('sales.SalesOrderDetail', 1) WITH PHYSICAL_ONLY GO --Check for constraints violation on specified table or indexed view DBCC CHECKCONSTRAINTS ('sales.SalesOrderDetail') GO
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.