SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Mighty

I'm a passionate SQL Server professional with 10+ years of experience with databases. Developer, Administrator and Architect with 10+ years of expertise in data analysis, design, programming, performance tuning, upgrades, migrations, high availability solutions implementation, backup & recovery strategies and database capacity planning expertise.

SQL Server: DBCC CHECKDB Command with Complete Explanation

SQL Server is used widely for the management of database. It is possible that the data gets corrupt due to different reasons. Therefore, to deal with this issue SQL Server has a command, which act as a manual solution for corruption of the database. This command is referred as DBCC CHECKDB.
DBCC CHECKDB is a useful command often used to check the consistency error. This helps the users from high level of database corruption. The command proves to be useful when it comes to the information of physical as well as the logical integrity of the specified database objects.
DBCC CHECKDB command in SQL Server
database_name | database_id | 0
It is the ID or name of the database for which the checking is performed. In case, not specified or 0, then the current database is used. Name of the database must comply with the name of the rules for identifiers.
NOINDEX
Indicates checks nonclustered indexes for user tables should not be performed. This reduces the overall time of execution. It does not have any effect on the system because integrity checks are performed on system table indexes.
REPAIR_ALLOW_DATA_LOSS|REPAIR_FAST|REPAIR_REBUILD
REPAIR_ALLOW_DATA_LOSS
This command tries for the repairing of all the errors that are reported. It is possible that you may loss some data, which is not repairable.
REPAIR_FAST
For backward compatibility maintains syntax. With this no recover actions are performed.
REPAIR_REBUILD
Repairs the data, which has no possibility of the loss of data. This includes quick repairs, like repairing absent rows in non-clustered indexes, and some time-consuming repairs, like rebuilding an index.
It is not possible to REPAIR_REBUILD, FILESTREAM data.
Note:Since DBCC CHECKDB with any of the Repair option is recoverable, therefore it is recommended to use CHECKDB
ALL_ERRORMSGS
Shows all reported errors per object by default. If the option is omitted and specified, it has no effect. Object ID sort all error, except messages that are generated from tempdb database.
EXTENDED_LOGICAL_CHECKS
If in SQL Server 2008, compatibility level is 100 or above, you need to do logical consistency checks on XML indexes, spatial indexes, and indexed view, where present.
NO_INFOMSGS
Restrains all informational messages.
TABLOCK
TABLOCK causes DBCC CHECKDB to run in a fast mode under heavily loaded database. However, it decreases the concurrency available on the database during the running period of DBCC CHECKDB.
ESTIMATEONLY
Provide the estimated space of tempdb space, which is required to run DBCC CHECKDB.
PHYSICAL_ONLY
Provides the checking of the integrity of the physical structure of the page. It record headers and the allocation consistency of SQL Server database. This check provides a small check of the physical consistency of the database. However, it can also expose checksum failures, common hardware failure, and torn pages.
With the help of PHYSICAL_ONLY option, the time period can be shorten to run DBCC CHECKDB on large databases. However, it is recommended to perform full DBCC CHECKDB command time to time.
PHYSICAL_ONLY does not allows any repair option and always implies NO_INFOMSGS
DATA_PURITY
Check the database for column values, which are out-of-range or invalid
Column-value integrity checks are enabled by default. It does not require the DATA_PURITY option. For those databases, which are upgraded to new versions of SQL Server, then by default, column-value checks are not enabled. Therefore to make them enable run DBCC CHECKDB WITH DATA_PURITY error free on the database. Once this is done, DBCC CHECKDB checks column-value integrity by default.
MAXDOP
When, on a system, the instance of SQL Server runs, which has more than one CPU or microprocessor, MAXDOP detects the best degree of parallelism, which shows the the number of processors used or involved to run one statement, for each parallel plan execution.
It overrides themaximums degree of parallelism
Note:if MAXDOP is set to zero, then the server will choose the maximum degree of parallelism.
Conclusion
In the above section, we have discussed the importance of DBCC CHECKDB command in SQL Server. We have also, learned the circumstances where the need of this command arises in front of the users.

Comments

Leave a comment on the original post [www.sqlmighty.com, opens in a new window]

Loading comments...