We know that in the development and application of the database system, we must ensure the integrity and consistency of the database. When the database has a serious error; when we suspect that the database is destroyed (if the database or object cannot be deleted, using a table, "unreliable data" information, etc.); when the user changed the default sort of Server The order or change the character set and needs to be checked; when SA is regularly checked for the system; these times, we need to use the Database Consistenecy Checker, referred to as DBCC. DBCC is a practical command set to check the logic consistency and physical consistency of a database. In development and application, DBCC is the command we often use.
The format of the dbcc command is as follows
DBCC (CHECKTABLE ((Table | Table ID ([, Skip_ncindex]) | Checkdb [(Database Name [, Skip_ncindex])] | Checkalloc [(Database Name [, FIX | NOFIX])] | TABLEAlLoc ({Tenn | Table Identification} [, {full | Optimized | FAST | NULL} [, FIX | NOFIX]]] | Indexalloc ({Table Name | Table Identifier}, Index Number [, {Full | Optimezed | Fast | Null} [, FIX | NOFIX]]) | Checkcatalog [(Database Name)] | DBREPAIR (Database Name, DropDB) | ReIndex ({Table Name | Table ID}) | fix_text ({Table Name | Table Identification)}
DBCC's permissions, for CheckTable, Fix_Text and Reindex are the owners that are default assigned to Checkdb, Checkalloc, Checkcatalog, DBREPAIR, INDEXALLOC, and TABLEALLOC are default assigned to the database primary. DBO automatically obtains permissions for DBCC commands and all options. This permission is uncommitted. In addition, DBCC is running while the database is active, except for DBREPAIR options and DBCC Checkalloc with FIX options.
CheckTable option
CheckTable is used to check a specified table, ensuring that the index and data pages are properly connected, and the index is stored in the correct order, all pointers consistency, the rationality of data information on each page, the rationality of page offset. If the log segment on its own (log) device, use the dbcc checktable command to the syslogs table to report the useful and remaining log space, use the Skip_ncindex option to make DBCC CheckTable skip the non-clustered index on the user table (Nonclustered Index) Check. The default is to check all indexes.
Example 1. Check the amount of space used by logs and unused space:
DBCC CheckTable (Syslogs)
If the log segment is on the log device, you will return the following information:
checking syslogs The total number of data page in the table is 1. NOTICE: Space used on the log segment is 0.20 Mbytes, 0.13% NOTICE:.. Space free on the log segment is 153.4Mbytes, 99.87% DBCC execution Completed.If dbcc Printed Error Messages, Contact A User with Sa Role.
If the log is not on its own device, the following information will be displayed: NOTICE: NOTIFICATION OF LOG SPACE USED / Free. CAN NOT BE Reported Because The log segment is not on Its Own Device. Example 2. DBCC CheckTable (Titles) The Total Number of data page in this table is 3. Table HAS 18 DATA ROWS. DBCC Execution completed. If DBCC Printed Error Messages. Contact A User with Sa Role.
CHECKDB option
Running the checkdb option is the same as the content CheckTable check, but it is doing such an inspection on each table in a specified database. If the database name is not specified, Checkdb checks the current database. The information returned by Checkdb also is with CheckTable.
Checkalloc option
Checkalloc is the case where you check the specified database to see all the correctly allocated pages, and the page that has not been assigned. If the database name is not specified, checkalloc checks the current database. Checkalloc will return the number of spaces assigned and used. Checkalloc's default mode is Nofix. To use the FIX option, you must place the database in a single user mode.
example:
DBCC Checkalloc (Pubs2).... Alloc page 0 (#of) = 68 ref pages = 68) Alloc Page 256 (# = 32 Used Pages = 154 Ref Pages = 154) Alloc Page 512 (# f EXTENT = 28 Used Pages = 184 Ref Pages = 184) Alloc Page 768 (# = 1 Used Pages = 1 Ref Pages = 1) Total (# = 93 Used Pages = 407 Ref Pages = 407) In this database. DBCC Execution Completed.if DBCC Printed Error Message, Contact A User With System Adminstrator (SA) Role.
TableAlloc Options
TableAlloc Checks the specified table to ensure that all pages are properly assigned. It is a narrowed version of Checkalloc. The same integrity check is performed on a single sheet. Three types of reports can be generated using TableAlloc: Full, Optimized and FAST. The Full option is equivalent to the checkallocock of the table, which reports various types of assignment errors. Optimized options Reports reports listed on the table-based object assignment image (OAM) page. It does not report, nor can it be sorted out in the OAM page that is not listed on the allocation page is not referenced (Extent). If the type is not specified or NULL, the Optimized option is the default setting. The FAST option does not generate allocation reports, but generates an additional report that is referenced but does not assign in the extension. The FIX | NOFIX option determines whether TableAlloc is finishing the assignment errors found in the table. For all tables, default is fixed, but the system tables are default Nofix. To use the FIX option to the system table, you must first place the database to schedule user mode.
example:
DBCC TableAlloc (Titles) Display information is as follows: The Default Report Option of Optimized IS Used for this Run. The default fix..is buy for this run.... Total #of extent = 3 Alloc Page 256 (# f e x = 1 Used pages = 2 Ref pages = 2). Alloc page 256 (# = 1 Used pages = 2 ref pages = 2) Alloc page 256 (# = 1 Used Pages = 2 Ref pages = 2) Total # of eXtent = 3 Used pages = 8 ref pages = 8) in this database.indexalloc option
Indexalloc Checks the specified index, make sure all the pages are properly assigned, which is the reduced version of Checkalloc, specify the same integrity check for a separate index. The options are the same as TableAlloc.
Checkcatalog options
The CheckCatalog option is used to check the consistency between the system tables within the system table. For example: it ensures that each (data) type in the syscolumns table has a matching record in the System IPES table; each table and view in sysObjects should be related to the description records of each of them in the syscolumns table; Make sure that the last checkpoint in syslogs is valid. CheckCatalog also reports any defined segments. If you do not specify a database name, check the current database.
DBREPAIR option
DBREPAIR (Database Name, DROPDB) option is to delete a broken database. The damaged database cannot be deleted with the drop database command. DROP DATABASE can only delete the normal database. When executing the dbrepAir command, any user (including the execution of this command) may not use the database being deleted. This option is to run in the Master library.
Reindex option
The Reindex option checks the integrity of the index on the user table by running the "FAST" execution of DBCC CheckTable. If it detects that the index is problematic, the index will be removed and rebuilt. After the order of the SQL Server is changed, the SA or the Lord should perform this option. This option cannot be run in user-defined transactions.
example:
DBCC ReIndex (Titles) Returns: One or More Indexes Corrupt.They Will Be Rebuilt.
FIX_TEXT option
After the SQL Server character set is converted by the single byte to multiple bytes, the FIX_TEXT option is used to upgrade the text value. SQL Server's character set is more complicated by single-byte transition to multi-byte character gathering to make text data more complicated. Since the text value may be sufficient to overwrite several pages, SQL Server must be able to process (via page constraint) that may be across the character. To do this, the server needs to add some information on each text page. The SA or table owner must run dbcc fix_text on each table of text data to calculate the number of new pages you need.
In summary, the information returned by the DBCC command accurately reflects the status of the database and its objects, which is a good helper we detect the database.
