| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Reference Manual Volumes 1 - 4 (Online Only) |
![[-]](/dwicons/i_colpse.gif)
| Chapter 13 dbccdb Tables |
Chapter 13
In addition to the standard system tables included in all databases, the dbcc management database, dbccdb, contains seven tables that define inputs to and outputs from dbcc checkstorage. It also contains at least two workspaces.
Workspaces are special tables in dbccdb that store intermediate results of the dbcc checkstorage operation. Workspaces differ from worktables in that they:
Are preallocated contiguously to improve I/O performance
Are persistent
Do not reside in the tempdb database
When you create dbccdb, two workspaces, scan and text, are created automatically. They are preallocated as follows:
Scan workspace - contains a row for each page of the target database. The allocation is approximately 1 percent of the database size. Each row consists of a single binary(18) column.
Text workspace - contains a row for each table in the target database that contains text or image columns. The size of this table depends on the design of the target database, but it is usually significantly smaller than the scan workspace. Each row consists of a single binary(22) column.
If either allocation is larger than needed by dbcc checkstorage, the operation uses only what it requires. The allocation does not change. If the text workspace allocation is too small, dbcc checkstorage reports this, recommends a new size, and continues checking; however, not all text chains are checked. If the scan workspace allocation is too small, the dbcc checkstorage operation fails immediately.
You must have at least one scan and one text workspace, but you may create as many as you need. While in use, the workspaces are locked so that only one dbcc checkstorage operation can use them at any given time. You can execute concurrent dbcc checkstorage operations by supplying each one with a separate scan and text workspace.
For more information on creating workspaces, see the System Administration Guide and the Adaptive Server Reference Manual.
Ideally, workspaces should be accessed only through dbcc checkstorage, but this is not a requirement. dbcc checkstorage exclusively locks the workspaces it uses, and the content of the workspaces is regenerated with each execution of dbcc checkstorage. The workspaces do not contain any secure data.
While the contents of the workspaces are accessible through SQL, no interpretation of the binary values is available. Access through SQL might return data from different dbcc checks mixed together. The presence of a row in these tables does not ensure that it contains valid data. dbcc tracks valid rows only during execution. That information is lost when the operation completes.
Most of the update activity in dbccdb is performed in the text and scan workspaces. The workspaces are preallocated, and only one dbcc checkstorage operation can use the workspaces at any given time, so the workspaces are less susceptible to corruption than most user tables. Corruption in a workspace can cause the dbcc checkstorage operation to fail or behave erratically. If this happens, drop and re-create the corrupt workspace.
Checks of databases using different workspaces can proceed simultaneously, but the performance of each operation might be degraded as it competes for disk throughput.
To delete a workspace, in dbccdb, enter:
drop table workspace_name
The results of each dbcc checkstorage operation are recorded in the dbccdb log. Updates to the text and scan workspaces are not recorded there.
The dbccdb log must be sized to handle updates to the tables. The log requirement is related to the number of tables and indexes in the target database. It is not related to the target database size.
To minimize the log requirement and the recovery time, use the truncate log on checkpoint option with dbccdb.
The dbcc_config table describes the currently executing or last completed dbcc checkstorage operation. It defines:
The location of resources dedicated to the dbcc checkstorage operation
Resource usage limits for the dbcc checkstorage operation
The columns for dbcc_config are:
Column name | Datatype | Description |
dbid | smallint | Matches the dbid from a row in sysindatabases. |
type_code | int | Matches the type_code from a row in the dbcc_types table. Valid values are 1-9. |
value | int | Specifies the value of the item identified by type_code. Can be null only if the value of stringvalue is not null. |
stringvalue | varchar(255) | Specifies the value of the item identified by type_code. Can be null only if the value of value is not null. |
Combination of dbid and type_code
For information on initializing and updating dbcc_config, see the System Administration Guide.
The dbcc_counters table stores the results of the analysis performed by dbcc checkstorage. Counters are maintained for each database, table, index, partition, device, and invocation of dbcc.
The columns for dbcc_counters are:
Column name | Datatype | Description |
dbid | smallint | Identifies the target database. |
id | int | Identifies the table. The value is derived from sysindexes and sysobjects. |
indid | smallint | Identifies the index. The value is derived from sysindexes. |
partitionid | smallint | Identifies the defined object-page affinity. The value is derived from sysindexes and syspartitions. |
devid | smallint | Identifies the disk device. The value is derived from sysdevices. |
opid | smallint | Identifies the dbcc operation that was performed. |
type_code | int | Matches the type_code column of a row in the dbcc_types table. Valid values are 5000 through 5024. |
value | real | Matches the appropriate type_name for the given type_code as described in dbcc_types. |
Combination of dbid, id, indid, partitionid, devid, opid, and type_code
The dbcc_fault_params table provides additional descriptive information for a fault entered in the dbcc_faults table.
The columns for dbcc_fault_params are:
Column name | Datatype | Description |
dbid | smallint | Identifies the target database. |
opid | smallint | Identifies the dbcc operation that was performed. |
faultid | int | Identifies the fault ID. |
type_code | int | Defines the interpretation of the value, which is provided by the "value" columns. Valid values are 1000-1009. They are described in dbcc_types. |
intvalue | int | Specifies the integer value. |
realvalue | real | Specifies the real value. |
binaryvalue | varbinary(255) | Specifies the binary value. |
stringvalue | varchar(255) | Specifies the string value. |
datevalue | datetime | Specifies the date value. |
Each "value" column (intvalue, realvalue, binaryvalue, stringvalue, and datevalue) can contain a null value. At least one must not be null. If more than one of these columns contains a value other than null, the columns provide different representations of the same value.
Combination of dbid, opid, faultid, and type_code
The dbcc_faults table provides a description of each fault detected by dbcc checkstorage.
The columns for dbcc_faults are:
Column name | Datatype | Description |
dbid | smallint | Identifies the target database. |
id | smallint | Identifies the table. The value is derived from sysindexes and sysobjects. |
indid | smallint | Identifies the index. The value is derived from sysindexes. |
partitionid | smallint | Identifies the partition. The value is derived from sysindexes and syspartitions. Counters are maintained for page ranges, so "partition" refers to the defined object-page affinity, rather than the actual object page chain. |
devid | smallint | Identifies the disk device. The value is derived from sysdevices |
opid | smallint | Identifies the dbcc operation that was performed. |
faultid | int | Provides a unique sequence number assigned to each fault recorded for the operation. |
type_code | int | Identifies the type of fault. Valid values are 100000-100032. They are described in Table 13-1. |
status | int | Classifies the fault. Valid values are:
|
Combination of dbid, id, indid, partitionid, devid, opid, faultid, and type_code
The dbcc_operation_log table records the use of the dbcc checkstorage operations.
The columns for dbcc_operaiton_log are:
Column Name | Datatype | Description |
dbid | smallint | Identifies the target database |
opid | smallint | Identifies the sequence number of the dbcc checkstorage operation. opid is an automatically incrementing number, unique for each dbid and finish pair. |
optype | smallint | The following value is valid for optype:
|
suid | int | Identifies the user executing the command |
start | datetime | Identifies when the operation started |
finish | datetime | Identifies when the operation ended |
Summary results are recorded in the dbcc_operation_results table.
Combination of dbid, opid, and optype
The dbcc_operation_results table provides additional descriptive information for an operation recorded in the dbcc_operation_log table.
The columns for dbcc_operation_results are:
Column Name | Datatype | Description |
dbid | smallint | Identifies the target database |
opid | smallint | Identifies the dbcc operation ID |
optype | smallint | Identifies the dbcc operation type |
type_code | int | Defines the dbcc operation type. Valid values are 1000-1007. They are described in Table 13-1. |
intvalue | int | Specifies the integer value |
realvalue | real | Specifies the real value |
binaryvalue | varbinary(255) | Specifies the binary value |
stringvalue | varchar(255) | Specifies the string value |
datevalue | datetime | Specifies the date value |
Each "value" column (intvalue, realvalue, binaryvalue, stringvalue, and datevalue) may contain a null value. At least one is not null. If more than one of these columns contains a value other than null, the columns provide different representations of the same value.
Results of the dbcc checkstorage operations include the number of:
Hard faults found
Soft faults found
Operations stopped due to a hard error
Combination of dbid, opid, optype, and type_code
The dbcc_types table provides the definitions of the data types used by dbcc checkstorage. This table is not actually used by the dbcc stored procedures. It is provided to facilitate the use of the other tables in dbccdb, and to document the semantics of the data types. Type codes for operation configuration, analysis data reported, fault classification, and fault report parameters are included. If you create your own stored procedures for generating reports, the values listed in the type_name column can be used as report headings.
The columns for dbcc_types are as follows.
To allow for future additions to dbcc_types, some type_code numbers are not used at this time:
type_code | type_name | Description |
1 | max worker processes | Optional - Specifies the maximum number of worker processes that can be employed. This is also the maximum level of concurrent processing used. Minimum value is 1. |
2 | dbcc named cache | Specifies the size (in kilobytes) of the cache used by dbcc checkstorage and the name of that cache. |
3 | scan workspace | Specifies the ID and name of the workspace to be used by the database scan. |
4 | text workspace | Specifies the ID and name of the workspace to be used for text columns. |
5 | operation sequence number | Specifies the number that identifies the dbcc operation that was started most recently. |
6 | database name | Specifies the name of the database in sysdatabases. |
7 | OAM count threshold | Specifies the percentage by which the OAM counts must vary before they can be considered to be an error. |
8 | IO error abort | Specifies the number of I/O errors allowed on a disk before dbcc stops checking the pages on that disk. |
9 | linkage error abort | Specifies the number of linkage errors allowed before dbcc stops checking the page chains of an object. Some kinds of page chain corruptions might require a check to be stopped with fewer linkage error than other kinds of page chain corruptions. |
1000 | hard fault count | Specifies the number of persistent inconsistencies (hard faults) found during the consistency check. |
1001 | soft fault count | Specifies the number of suspect conditions (soft faults) found during the consistency check. |
1002 | checks aborted count | Specifies the number of linkage checks that were stopped during the consistency check. |
1007 | text column count | Specifies the number of non-null text/image column values found during the consistency check. |
5000 | bytes data | Specifies (in bytes) the amount of user data stored in the partition being checked. |
5001 | bytes used | Specifies (in bytes) the amount of storage used to record the data in the partition being checked. The difference between bytes used and bytes data shows the amount of overhead needed to store or index the data. |
5002 | pages used | Specifies the number of pages linked to the object being checked that are actually used to hold the object. |
5003 | pages reserved | Specifies the number of pages that are reserved for the object being checked, but that are not allocated for use by that object. The difference between (8 * extents used) and (pages used + pages reserved) shows the total uncommitted deallocations and pages incorrectly allocated. |
5004 | pages overhead | Specifies the number of pages used for the overhead functions such as OAM pages or index statistics. |
5005 | extents used | Specifies the number of extents allocated to the object in the partition being checked. For object 99 (allocation pages), this value is the number of extents that are not allocated to a valid object. Object 99 contains the storage that is not allocated to other objects. |
5006 | count | Specifies the number of component items (rows or keys) found on any page in the part of the object being checked. |
5007 | max count | Specifies the maximum number of component items found on any page in the part of the object being checked. |
5008 | max size | Specifies the maximum size of any component item found on any page in the part of the object being checked. |
5009 | max level | Specifies the maximum number of levels in an index. This datatype is not applicable to tables. |
5010 | pages misallocated | Specifies the number of pages that are allocated to the object, but are not initialized correctly. It is a fault counter. |
5011 | io errors | Specifies the number of I/O errors encountered. This datatype is a fault counter. |
5012 | page format errors | Specifies the number of page format errors reported. This datatype is a fault counter. |
5013 | pages not allocated | Specifies the number of pages linked to the object through its chain, but not allocated. This datatype is a fault counter. |
5014 | pages not referenced | Specifies the number of pages allocated to the object, but not reached through its chains. This datatype is a fault counter. |
5015 | overflow pages | Specifies the number of overflow pages encountered. This datatype is only applicable to clustered indexes. |
5016 | page gaps | Specifies the number of pages not linked to the next page in ascending sequence. This number indicates the amount of table fragmentation. |
5017 | page extent crosses | Specifies the number of pages that are linked to pages outside of their own extent. As the number of page extent crosses increases relative to pages used or extents used, the effectiveness of large I/O buffers decreases. |
5018 | page extent gaps | Specifies the number of page extent crosses where the subsequent extent is not the next extent in ascending sequence. Maximal I/O performance on a full scan is achieved when the number of page extent gaps is minimized. A seek or full disk rotation is likely for each gap. |
5019 | ws buffer crosses | Specifies the number of pages that are linked outside of their workspace buffer cache during the dbcc checkstorage operation. This information can be used to size the cache, which provides high performance without wasting resources. |
5020 | deleted rows | Deleted rows, the number of deleted rows in the object |
5021 | forwarded rows | Forwarded rows, number of forwarded rows in the object |
5022 | empty pages | The number of pages allocated but not containing data |
5023 | pages with garbage | Pages with garbage, number of pages that could benefit from garbage collection |
5024 | non-contiguous free space. | Non-contiguous free space, number of bytes of non-contiguous free space |
10000 | page id | Specifies the location in the database of the page that was being checked when the fault was detected. All localized faults include this parameter. |
10001 | page header | Specifies the hexadecimal representation of the header of the page that was being checked when the fault was detected. This information is useful for evaluating soft faults and for determining if the page has been updated since it was checked. The server truncates trailing zeros. |
10002 | text column id | Specifies an 8-byte hexadecimal value that gives the page, row, and column of the reference to a text chain that had a fault. The server truncates trailing zeros. |
10003 | object id | Specifies a 9-byte hexadecimal value that provides the object id (table), the partition id (partition of the table) if applicable, and the index id (index) of the page or allocation being checked. For example, if a page is expected to belong to table T1 because it is reached from T1's chain, but is actually allocated to table T2, the object id for T1 is recorded, and the object id expected for T2 is recorded. The server truncates trailing zeros. |
10007 | page id expected | Specifies the page ID that is expected for the linked page when there is a discrepancy between the page ID that is expected and the page ID that is actually encountered. For example, if you follow the chain from P1 to P2 when going forward, then, when going backward, P1 is expected to come after P2. The value of page id expected is P1, and the value of page id is P2. When the actual value of P3 is encountered, it is recorded as page id actual. |
10008 | page id actual | When there is a discrepancy between the page ID that is encountered and the expected page ID, this value specifies the actual page ID that is encountered. (See also, type_code 10007.) For example, if you follow the chain from P1 to P2 when going forward, then, when going backward, P1 is expected to come after P2. The value of page id expected is P1, and the value of page id is P2. When the actual value of P3 is encountered, it is recorded as page id actual. |
10009 | object id expected | Specifies a 9-byte hexadecimal value that provides the expected object id (table), the partition id (partition of the table) if applicable, and the index id (index) of the page or allocation being checked. For example, if a page is expected to belong to table T1 because it is reached from T1's chain, but is actually allocated to table T2, the object id for T1 is recorded, and the object id expected for T2 is recorded. The server truncates trailing zeros. |
10010 | data-only locked data page header | Indicates the 44 byte page header for the page where the fault is located |
10011 | data-only locked b-tree leaf page header | Indicates the 44 byte page header for the page where the fault is located |
10012 | data-only locked b-tree header | Indicates the 44 byte page header for the page where the fault is located |
100000 | IO error | Indicates that part of the identified page could not be fetched from the device. This is usually caused by a failure of the operating system or the hardware. |
100001 | page id error | Indicates that the identifying ID (page number) recorded on the page is not valid. This might be the result of a page being written to or read from the wrong disk location, corruption of a page either before or as it is being written, or allocation of a page without subsequent initialization of that page. |
100002 | page free offset error | Indicates that the end of data on a page is not valid. This event affects insertions and updates on this page. It might affect some access to the data on this page. |
100003 | page object id error | Indicates that the page appears to be allocated to some other table than the one expected. If this is a persistent fault, it might be the consequence of either:
|
100004 | timestamp error | Indicates that the page has a timestamp that is later than the database timestamp. This error can result in failure to recover when changes are made to this page. |
100005 | wrong dbid error | Indicates that the database ID dbid is stored on the database allocation pages. When this ID is incorrect, the allocation page is corrupt and all the indicated allocations are suspect. |
100006 | wrong object error | Indicates that the page allocation is inconsistent. The page appears to belong to one table or index, but it is recorded as being allocated to some other table or index in the allocation page. This error differs from page object id error in that the allocation is inconsistent, but the consequences are similar. |
100007 | extent id error | Indicates that an allocation was found for a table or index that is unknown to dbcc checkstorage. Typically, this results in the inability to use the allocated storage. |
100008 | fixed format error | Indicates that the page incorrectly indicates that it contains only rows of a single fixed length. dbcc checkstorage reports this error. dbcc checktable does not report it, but does repair it. |
100009 | row format error | Indicates that at least one row on the page is incorrectly formatted. This error might cause loss of access to some or all the data on this page. |
100010 | row offset error | Indicates that at least one row on the page is not located at the expected page offset. This error might cause loss of access to some or all of the data on this page. |
100011 | text pointer error | Indicates that the location of the table row that points to the corrupted text or image data. This information might be useful for correcting the problem. |
100012 | wrong type error | Indicates that the page has the wrong format. For example, a data page was found in an index or a text/image column. |
100013 | non-OAM error | This error is a special case of wrong type error. It is not reported as a separate condition in the current release. |
100014 | reused page error | Indicates that a page is reached by more than one chain and that the chains belong to different objects. This error indicates illegal sharing of a page through corrupt page chain linkages. Access to data in either or both tables might be affected. |
100015 | page loop error | Indicates that a page is reached a second time while following the page chain for an object, which indicates a loop in the page chain. A loop can result in a session hanging indefinitely while accessing data in that object. |
100016 | OAM ring error | Indicates that a page is allocated but not reached by the page chains for the object.Typically, this results in the inability to use the allocated storage. |
100017 | OAM ring error | Indicates that the OAM page ring linkages are corrupted. This might not affect access to the data for this object, but it might affect insertions, deletions, and updates to that data. |
100018 | missing OAM error | Indicates that dbcc checkstorage found an allocation for the object that was not recorded in the OAM. This error indicates a corruption that might affect future allocations of storage, but probably does not affect access to the presently stored data. |
100019 | extra OAM error | Indicates that an allocation for this object was recorded in the OAM, but it was not verified in the allocation page. This error indicates a corruption that might affect future allocations of storage, but probably does not affect access to the presently stored data. |
100020 | check aborted error | Indicates that dbcc checkstorage stopped checking the table or index. To prevent multiple fault reports, the check operation on a single chain might be stopped without reporting this error. When an object contains several page chains, failure of the check operation for one chain does not prevent the continuation of the check operation on the other chains unless a fault threshold is exceeded. |
100021 | chain end error | Indicates that the end of the chain is corrupted. As a soft fault, it might indicate only that the chain was extended or truncated by more than a few pages during the dbcc checkstorage operation. |
100022 | chain start error | Indicates that the start of a chain is corrupted or is not at the expected location. If this is a persistent fault, access to data stored in the object is probably affected. |
100023 | used count error | Indicates an inconsistency between the count of the pages used that is recorded in the OAM page and the count of the pages used that is determined by examining the allocation pages. |
100024 | unused count error | Indicates an inconsistency between the count of the pages reserved but unused that is recorded in the OAM page and the count of the pages reserved but unused that is determined by examining the allocation pages. |
100025 | row count error | Indicates an inconsistency between the row count recorded in the OAM page and the row count determined by dbcc checkstorage. |
100026 | serialloc error | Indicates a violation of the serial allocation rules applied to log allocations. |
100027 | text root error | Indicates a violation of the format of the root page of a text or image index. This check is similar to the root page checks performed by dbcc textalloc. |
100028 | page misplaced | Indicates that pages of this object were not found where they were expected to be from examination of the system tables. This usually indicates that sp_placeobject was used sometime in the past. In the dbcc_counters table, all misplaced pages are counted together, rather than being reported by device and partition. |
100029 | page header error | Indicates an internal inconsistency in the page's header other than the kind described by the other type codes. The severity of this error depends on the type of page and the inconsistency found. |
100030 | page format error | Indicates an internal inconsistency in the page's body other than the kind described by the other type codes. The severity of this error depends on the type of page and the inconsistency found. |
100031 | page not allocated | Indicates that dbcc checkstorage reached an unallocated page by following a page chain. This condition might affect access to data stored in this object. |
100032 | page linkage error | Indicates that dbcc checkstorage detected a fault with either the next or previous linkage of an interior page of a chain. If this is a persistent fault, access to data stored in the object is probably affected. |
100033 | non-contiguous free-space error | Indicates an invalid or inconsistent value for the non-contiguous free space on the page |
100034 | insert free space error | Indicates an invalid or inconsistent value for the contiguous free space on the page |
100035 | spacebits mismatch | Indicates an inconsistency in the page fullness indicator |
100036 | deleted row count error | Indicates an invalid or inconsistent value for the deleted row count on the page |
100037 | Forwarded rows error | Indicates an inconsistency between the forwarded rows indicator and the number of forwarded rows on the page |
100038 | Page header type error | Indicates that a Page header format indicator set incorrectly |
|
|