The Transact-SQL language supports the DBCC (Database Console Commands) statement that act as a command for the Database Engine. Depending on the options used with DBCC, the DBCC commands can be divided into the following groups:
- Maintenance
- Informational
- Validation
- Miscellaneous
Table 15-4 Most Important Options of the sqlservr Utility
| Option | Description |
|---|---|
| –f | Indicates that the instance is started with the minimal configuration. |
| –m | Indicates that the instance is started in single-user mode. Use this option if you have problems with the system and want to perform maintenance on it (this option must be used to restore the master database). |
| –s instance_name | Specifies the instance of the Database Engine. If no named instance is specified, sqlservr starts the default instance of the Database Engine. |
Validation Commands
The validation commands do consistency checking of the database. The following commands belong to this group:
- DBCC CHECKALLOC
- DBCC CHECKTABLE
- DBCC CHECKCATALOG
- DBCC CHECKDB
The DBCC CHECKALLOC command validates whether every extent indicated by the system has been allocated, as well as that there are no allocated extents that are not indicated by the system. Therefore, this command performs cross-referencing checks for extents.
The DBCC CHECKTABLE command checks the integrity of all the pages and structures that make up the table or indexed view. All performed checks are both physical and logical. The physical checks control the integrity of the physical structure of the page. The logical checks control, among other things, whether every row in the base table has a matching row in each nonclustered index, and vice versa, and whether indices are in their correct sort order. Using the PHYSICAL_ONLY option, you can validate only the physical structure of the page. This option causes a much shorter execution time of the command and is therefore recommended for frequent use on production systems.
The DBCC CHECKCATALOG command checks for catalog consistency within the specified database. It performs many cross-referencing checks between tables in the system catalog. After the DBCC CATALOG command finishes, a message is written to the 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 because of an error, the message indicates the command was terminated, a state value, and the amount of time the command ran.
If you want to check the allocation and the structural and logical integrity of all the objects in the specified database, use DBCC CHECKDB. (As a matter of fact, this command performs all checks previously described, in the given order.)
