Database consistency checker (dbcc) checks the logical and physical consistency of a database and provides statistics, planning, and repair functionality.
Certain dbbc commands apply only to shared-disk clusters. See the separately listed dbbc syntax for clusters.
dbcc addtempdb (dbid |database_name)
dbcc checkalloc [(database_name[, fix | nofix])]
dbcc checkcatalog [(database_name[, fix])
dbcc checkdb [(database_name[, skip_ncindex])]
dbcc checkindex ({table_name | table_id}, index_id
[, bottom_up[, partition_name | partition_id]])
dbcc checkstorage [(database_name)]
dbcc checktable (table_name | table_id [, skip_ncindex | fix_spacebits | "check spacebits" | bottom_up | NULL[, partition_name | partition_id)
dbcc checkverify (dbname[, tblname[, ignore_exclusions]])
dbcc complete_xact (xid, {["commit", “1pc”] | "rollback"})
dbcc forget_xact (xid)
dbcc dbrepair (database_name, dropdb)
dbcc engine ({offline, [enginenum] | "online"})
dbcc fix_text ({table_name | table_id})
dbcc indexalloc (table_name | table_id, index_id [, optimized | fast | NULL [, fix | nofix | NULL [, partition_name | partition_id]]])
dbcc monitor (increment, <group name>)
dbcc monitor (decrement, <group name>)
dbcc monitor (reset, <group name>)
dbcc pravailabletempdbs
dbcc rebuild_text (table_name | table_id | "all"[, column[, text_page [, data_partition_name | data_partition_id]]])
dbcc reindex ({table_name | table_id})
dbcc serverlimits
dbcc stackused
dbcc tablealloc (table_name | table_id [, full | optimized | fast | NULL [, fix | nofix | NULL [, data_partition_name | data_partition_id]]])
dbcc textalloc (table_name | table_id [, full | optimized | fast | NULL [, fix | nofix | NULL [, data_partition_name | data_partition_id]]])
dbcc {traceon | traceoff} (flag [, flag ...])
dbcc tune ({ascinserts, {0 | 1} , table_name |
cleanup, {0 | 1} |
cpuaffinity, start_cpu {, on| off} |
des_greedyalloc, dbid, object_name,
" {on | off}" | deviochar vdevno, "batch_size" |
doneinproc {0 | 1}})
dbcc upgrade_object [ ( dbid | dbname [,[database.[owner].]compiled_object_name' | 'check' | 'default' | 'procedure' | 'rule' | 'trigger' | 'view' [, 'force' ] ] )
dbcc syntax for clusters only:
dbcc nodetraceon(trace_flag_number) dbcc nodetraceoff(trace_flag_number)
dbcc set_scope_in_cluster("cluster"|"instance"|"scope")
dbcc quorum
adds a temporary database to the global list of available temporary databases. If the database does not exist or is not a temporary database, an error is generated. If the database is already a member of the list, an informational message prints.
is the database ID.
is the name of the database to check. If no database name is given, dbcc uses the current database.
checks the specified database to see that all pages are correctly allocated and that no page that is allocated is not used. If no database name is given, checkalloc checks the current database. It always uses the optimized report option (see tablealloc).
checkalloc reports on the amount of space allocated and used.
determines whether dbcc fixes the allocation errors found. The default mode for checkalloc is nofix. You must put the database into single-user mode to use the fix option.
For a discussion of page allocation in Adaptive Server, see the System Administration Guide.
checks for consistency in and between system tables. For example, checkcatalog makes sure that every type in syscolumns has a matching entry in systypes, that every table and view in sysobjects has at least one column in syscolumns, and that the last checkpoint in syslogs is valid. For a complete list of the checking that checkcatalog performs, see “Checking performed by dbcc checkcatalog”. You can use checkcatalog in an archive database, but not the fix version of checkcatalog.
checkcatalog also reports on any segments that have been defined. If no database name is given, checkcatalog checks the current database.
determines whether dbcc fixes the sysindexes errors it finds. The default mode for checkcatalog is to not fix the errors. You must put the database into singleuser mode to use the fix option. The new sysindexes checks may result in new errors, not raised by dbcc checkcatalog in a Adaptive Servers earlier than version 12.5.2.
runs the same checks as checktable, but on each table, including syslogs, in the specified database. If no database name is given, checkdb checks the current database. You can use checkdb in an archive database.
causes dbcc checktable or dbcc checkdb to skip checking the nonclustered indexes on user tables. The default is to check all indexes.
runs the same checks as checktable, but only on the specified index. You can use checkindex in an archive database.
DOL tables only – checks indexes in a bottom-up order when specifying this option with checkindex. The bottom_up check involves verifying whether each datarow has a corresponding index row.
checks the specified database for allocation, object allocation map (OAM) page entries, page consistency, text valued columns, allocation of text valued columns, and text column chains. The results of each dbcc checkstorage operation are stored in the dbccdb database. For details on using dbcc checkstorage, and on creating, maintaining, and generating reports from dbccdb, see the System Administration Guide.
dbcc checkstorage reports a soft fault if any data page that is not the first data page is empty for non-hashed tables, when creating virtually-hashed tables. However, dbcc checkstorage does not report this soft fault for the hashed region of a virtually-hashed table. Any data page in the hashed region of a virtually-hashed table can be empty.
checks the specified table to see that index and data pages are correctly linked, that indexes are in properly sorted order, that all pointers are consistent, that the data information on each page is reasonable, and that page offsets are reasonable. You can use checktable in an archive database.
Certain changes to dbcc checktable refer to virtually-hashed tables:
In addition to the regular checks it performs, checktable verifies that the layout of data and OAM pages in the hash region is correct.
Data pages are not allocated in an extent reserved for OAM pages as per the layout.
The OAM pages are allocated only in the first extent of an allocation unit (AU)
is the name or object ID of the table to check.
is for tables of type datapages or datarows lockscheme, and checks for the validity of space bits and fixes any invalid space bits. Space bits are stored per page and indicate the room available in a page for new inserts.
checks space bits for tables of type datapages or datarows lockscheme. If you specify check spacebits, dbcc does not check nonclustered indexes.
DOL tables only – checkes indexes in a bottom-up order when specifying this option with checktable.
is the name or ID of the data partition to check. If you specify a partition, dbcc skips global indices.
verifies the results of the most recent run of dbcc checkstorage for the specified database. For details on using dbcc checkverify, see the System Administration Guide.
enables or disables the exclusion list. Value is either 0, the default (enables the exclusion list) or 1 (disables the exclusion list)
heuristically completes a transaction by either committing or rolling back its work. Adaptive Server retains information about all heuristically completed transactions in the master.dbo.systransactions table, so that the external transaction coordinator may have some knowledge of how the transaction was completed.
WARNING! Heuristically completing a transaction in the prepared state can cause inconsistent results for an entire distributed transaction. The System Administrator’s decision to heuristically commit or roll back a transaction may contradict the decision made by the coordinating Adaptive Server or protocol.
is a transaction name from the systransactions.xactname column. You can also determine valid xid values using sp_transactions.
heuristically completes a transaction that was subject to a one-phase commit protocol optimization—instead of the regular two-phase commit protocol—by the external transaction manager that was coordinating its completion. This option allows the heuristic commit of a transaction that was not in the prepared state.
removes the completion status of a heuristically completed transaction from master.dbo.systransactions. forget_xact can be used when the System Administrator does not want the coordinating service to have knowledge that a transaction was heuristically completed, or when an external coordinator is not available to clear commit status in systransactions.
WARNING! Do not use dbcc forget_xact in a normal DTP environment, since the external transaction coordinator should be permitted to detect heuristically-completed transactions. X/Open XA-compliant transaction managers and Adaptive Server transaction coordination services automatically clear the commit status in systransactions.
drops a damaged database. drop database does not work on a damaged database.
No one can use the database being dropped when this dbcc statement is issued (including the user issuing the statement).
takes Adaptive Server engines offline or brings them online. If enginenum is not specified, dbcc engine (offline) takes the highest-numbered engine offline. For more information, see Chapter 8, “Managing Multiprocessor Servers,” in the System Administration Guide.
upgrades text values after an Adaptive Server character set has been changed from any character set to a new multibyte character set.
Changing to a multibyte character set makes the internal management of text data more complicated. Since a text value can be large enough to cover several pages, Adaptive Server must be able to handle characters that span page boundaries. To do so, the server requires additional information on each of the text pages. The System Administrator or table owner must run dbcc fix_text on each table that has text data to calculate the new values needed. For more information, see the System Administration Guide.
checks the specified index to see that all pages are correctly allocated and that no page that is allocated is not used. This is a smaller version of checkalloc, providing the same integrity checks on an individual index. You can use indexalloc in an archive database.
indexalloc produces the same three types of reports as tablealloc: full, optimized, and fast. If no type is indicated, or if you use null, Adaptive Server uses optimized. The fix | nofix option functions the same with indexalloc as with tablealloc.
You
can specify fix or nofix only
if you include a value for the type of report (full, optimized, fast,
or null).
is the table’s name or the table’s object ID.
is the ID of the index that is checked during dbcc indexalloc.
is for tables of type datapages or datarows lockscheme, and checks for the validity of space bits and fixes any invalid space bits. Space bits are stored per page and indicate the room available in a page for new inserts.
checks space bits for tables of type datapages or datarows lockscheme. If you specify check spacebits, dbcc does not check nonclustered indexes.
reports all types of allocation errors.
produces a report based on the allocation pages listed in the object allocation map (OAM) pages for the index. It does not report and cannot fix unreferenced extents on allocation pages that are not listed in the OAM pages. The optimized option is the default.
does not produce an allocation report, but produces an exception report of pages that are referenced but not allocated in the extent (2521-level errors).
determines whether indexalloc fixes the allocation errors found in the table. The default is fix for all indexes except indexes on system tables, for which the default is nofix. To use the fix option with system tables, you must first put the database in single-user mode.
You can specify fix or nofix only if you include a value for the type of report (full, optimized, fast, or null).
if you specify a partition ID, allocation checks are performed on the partition identified by (indid, partition id).
prints the global list of available temporary databases.
rebuilds or creates an internal Adaptive Server 12.x data structure for text, or unitext, image data. This data structure enables Adaptive Server to perform random access and asynchronous prefetch during data queries.You can run rebuild_text on all tables in a database, a single table, or a data partition.
is the table’s name or the table’s object ID, or all the objects in the database
is the ID or name of the column of the text column. dbcc rebuild_text rebuilds the internal data structure of each text value of this column.
is the logical page number of the first text page. dbcc rebuild_text rebuilds the internal data structure of this text page.
is name or ID of the data partition. If you specify text_page, data_partition_name (or data_partition_id) is ignored.
The increment and decrement commands increase and decrease the usage counts for the monitor counters in the specified group by 1. The reset command sets the usage count for the monitor counters in the specified group to zero. This will turn off collection of monitoring data for this group.
group name can be one of the following:
‘all’ – determine usage count for the all group, which comprises most of the monitor counters, by selecting the @@monitors_active global variable.
spinlock_s – usage counts for spinlock_s reported by the dbcc resource command
appl – usage counts for appl reported by the dbcc resource command
checks the integrity of indexes on user tables by running a fast version of dbcc checktable. It can be used with the table name or the table’s object ID (the id column from sysobjects). reindex prints a message when it discovers the first index-related error, then drops and re-creates the suspect indexes. The System Administrator or table owner must run dbcc reindex after the Adaptive Server sort order has been changed and indexes have been marked “suspect” by Adaptive Server.
When dbcc finds corrupt indexes, it drops and re-creates the appropriate indexes. If the indexes for a table are already correct, or if the table has no indexes, dbcc reindex does not rebuild the index, but prints an informational message instead.
dbcc reindex aborts if a table is suspected of containing corrupt data. When that happens, an error message instructs the user to run dbcc checktable. dbcc reindex does not allow reindexing of system tables. System indexes are checked and rebuilt, if necessary, as an automatic part of recovery after Adaptive Server is restarted following a sort order change.
reports the maximum amount of stack memory used since the server first started.
display the limits Adaptive Server enforces on various entities. These include the lengths of identifiers and the maximum number of different objects such as number of columns in a table, number of indexes on a table, page sizes, row-overheads, and so on. Use the inforamtion to determine the various sizing characteristics of the Adaptive Server process.
checks the specified table or data partition to see that all pages are correctly allocated and that no page that is allocated is not used. This is a smaller version of checkalloc, providing the same integrity checks on an individual table. It can be used with the table name or the table’s object ID (the id column from sysobjects). You can use tablealloc in an archive database. For an example of tablealloc output, see the System Administration Guide.
Three types of reports can be generated with tablealloc: full, optimized, and fast. If no type is indicated, or if you use null, Adaptive Server uses optimized.
checks the allocation integrity of text or image pages in a database. You can use dbcc textalloc with an archive database.
is equivalent to checkalloc at a table level; it reports all types of allocation errors.
produces a report based on the allocation pages listed in the object allocation map (OAM) pages for the table. It does not report and cannot fix unreferenced extents on allocation pages that are not listed in the OAM pages. The optimized option is the default.
does not produce an allocation report, but produces an exception report of pages that are referenced but not allocated in the extent (2521-level errors).
determines whether or not tablealloc fixes the allocation errors found in the table. The default is fix for all tables except system tables, for which the default is nofix. To use the fix option with system tables, you must first put the database in single-user mode.
You can specify fix or nofix only if you include a value for the type of report (full, optimized, fast, or null).
is name or ID of the data partition to check. If you specify a partition, dbcc tablealloc skips global indexes.
toggles the printing of diagnostics during query optimization. Values 3604 and 3605 toggle, sending trace output to the user session and to the error log, respectively.
enables or disables tuning flags for special performance situations. You must reissue dbcc tune each time you restart Adaptive Server. For more information on the individual options, see Performance and Tuning Guide: Basics.
upgrades a compiled object from the text stored in the syscomments table. Its parameters are:
dbid – specifies the database ID. If you do not specify dbid, all compiled objects in the current database are upgraded.
dbname – specifies the database name. If you do not specify dbname, all compiled objects in the current database are upgraded.
compiled_object_name – is the name of a specific compiled object you want to upgrade. If you use the fully qualified name, dbname and database must match, and you must enclose the fully qualified name in quotes. If the database contains more than one compiled object of the same name, use the fully qualified name. Otherwise, all objects with the same name are parsed, and if no errors are found, upgraded.
check – upgrades all check constraints and rules. Referential constraints are not compiled objects and do not require upgrading.
default – upgrades all declarative defaults and the defaults created with the create default command.
procedure – upgrades all stored procedures.
rule – upgrades all rules and check constraints.
trigger – upgrades all triggers.
view – upgrades all views.
The keywords check, default, procedure, rule, trigger, and view specify the classes of compiled objects to be upgraded. When you specify a class, all objects in that class, in the specified database, are upgraded, provided that dbcc upgrade_object finds no errors or potential problem areas.
force specifies that you want to upgrade the specified object even if it contains a select * clause. Do not use force unless you have confirmed that the select * statement will not return unexpected results. The force option does not upgrade objects that contain reserved words, contain truncated or missing source text, refer to nonexistent temporary tables, or do not match the quoted identifier setting. You must fix these objects before they can be upgraded.
checks syntax for the specified compiled object in syscomments in the specified database. Does not raise errors on occurrences of select.
For upgrade_object, upgrades all check constraints and rules. Referential constraints are not compiled objects and do not require upgrading
forces an upgrade of the object from syscomments even if an upgrade is not required.
is the name of the compiled object.
is one of the following object types that Adaptive Server compiles: procedure, function, view, trigger, default, rule, condition.
is the name of a specific compiled object you want to upgrade. If you use the fully qualified name, database_name and database must match, and you must enclose the fully qualified name in quotes. If the database contains more than one compiled object of the same name, use the fully qualified name. Otherwise, all objects with the same name are parsed, and if no errors are found, upgraded.
upgrades all declarative defaults and the defaults created with the create default command.
upgrades all stored procedures.
upgrades all rules and check constraints.
upgrades all triggers.
upgrades all views.
The keywords check, default, procedure, rule, trigger, and view specify the classes of compiled objects to be upgraded. When you specify a class, all objects in that class, in the specified database, are upgraded, provided that dbcc upgrade_object finds no errors or potential problem areas.
specifies that you want to upgrade the specified object even if it contains a select * clause. Do not use force unless you have confirmed that the select * statement will not return unexpected results. The force option does not upgrade objects that contain reserved words, contain truncated or missing source text, refer to nonexistent temporary tables, or do not match the quoted identifier setting. You must fix these objects before they can be upgraded.
is the number of the trace flag you are enabling or disabling.
sets the dbcc command scope to the cluster. Subsequent dbcc commands have a cluster-wide effect.
sets the dbcc command scope to the current instance. Subsequent dbcc commands affect only the local instance.
displays the current scope of the dbcc command, either cluster or instance.
Checks pubs2 for page allocation errors:
dbcc checkalloc (pubs2)
Checks database consistency for pubs2 and places the information in the dbccdb database:
dbcc checkstorage (pubs2)
Checks the salesdetail table:
dbcc checktable (salesdetail)
Checking salesdetail The total number of pages in partition 1 is 3. The total number of pages in partition 2 is 1. The total number of pages in partition 3 is 1. The total number of pages in partition 4 is 1. The total number of data pages in this table is 10. Table has 116 data rows. DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Heuristically aborts the transaction, “distributedxact1:”
dbcc complete_xact (distributedxact1, "rollback")
Upgrades text values for blurbs after a character set change:
dbcc fix_text (blurbs)
Removes information for the transaction “distributedxact1” from master.dbo.systransactions:
dbcc forget_xact (distributedxact1)
Adaptive Server returns a full report of allocation for the index with an indid of 2 on the titleauthor table and fixes any allocation errors:
dbcc indexalloc ("pubs..titleauthor", 2, full)
Prints the global list of available temporary databases:
dbcc pravailabletempdbs
Available temporary databases are: Dbid: 2 Dbid: 4 Dbid: 5 Dbid: 6 Dbid: 7 DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Rebuilds or creates an internal Adaptive Server data structure for all text and image columns in the blurbs table:
dbcc rebuild_text (blurbs)
Checks part of the titles table that resides on the smallsales partition (which contains all booksales less than 5000)
dbcc checktable (titles, NULL, "smallsales")
dbcc reindex has discovered one or more corrupt indexes in the titles table:
dbcc reindex (titles)
One or more indexes are corrupt. They will be rebuilt.
Checks the maximum amount of stack memory used since Adaptive Server started:
dbcc stackused
Upgrades all stored procedures in the listdb database:
dbcc upgrade_object(listdb, 'procedure')
Upgrades all rules and check constraints in the listdb database. Double quotes are used around rule because set quoted identifiers is off.
dbcc upgrade_object(listdb, list_proc)
Displays an abridged output showing various forms of limits in your Adaptive Server:
dbcc serverlimits
Limits independent of page size: ================================ Server-wide, Database-specific limits and sizes Max engines per server : 128 Max number of logins per server : 2147516416 Max number of users per database : 2146484223 Max number of groups per database : 1032193 Max number of user-defined roles per server : 1024 Max number of user-defined roles per (user) session : 127 Min database page size : 2048 Max database page size : 16384 ... Database page-specific limits APL page header size : 32 DOL page header size : 44 Max reserved page gap : 255 Max fill factor : 100 Table, Index related limits Max number of columns in a table/view : 1024 Max number of indexes on a table : 250 Max number of user-keys in a single index on an unpartitioned table : 31 Max number of user-keys in a single local index on a partitioned table : 31 ... General SQL related Max size of character literals, sproc parameters : 16384 Max size of local @variables in T-SQL : 16384 Max number of arguments to stored procedures : 2048 Max number of arguments to dynamic SQL : 2048 Max number of aggregates in a COMPUTE clause : 254 ... Maximum lengths of different Identifiers Max length of server name : 30 Max length of host name : 30 Max length of login name : 30 Max length of user name : 30 ... Limits as a function of the page size: ====================================== Item dependent on page size : 2048 4096 8192 16384 -------------------------------------------------------------------------- Server-wide, Database-specific limits and sizes Min number of virtual pages in master device : 11780 22532 45060 90116 Default number of virtual pages in master device : 23556 45060 90116 180228 Min number of logical pages in master device : 11776 11264 11264 11264 Min number of logical pages in tempdb : 2048 1536 1536 1536 Table-specific row-size limits Max possible size of a log-record row on APL log page : 2014 4062 8158 16350 Physical Max size of an APL data row, incl row-overheads : 1962 4010 8106 16298 Physical Max size of a DOL data row, incl row-overheads : 1964 4012 8108 16300 Max user-visible size of an APL data row : 1960 4008 8104 16296 Max user-visible size of a DOL data row : 1958 4006 8102 16294 Max user-visible size of a fixed-length column in an APL table : 1960 4008 8104 16296 Max user-visible size of a fixed-length column in a DOL table : 1958 4006 8102 16294 ...
To show a complete listing of limits in the server,
execute dbcc traceon (3604) to get the output
to the client session.
Adaptive Server returns an optimized report of allocation for this table, but does not fix any allocation errors:
dbcc tablealloc (publishers, null, nofix)
Performs allocation checks on the smallsales partition. All the local indexes on smallsales are included in the check, while the global indexes are excluded:
dbcc tablealloc (titles, null, null, smallsales)
Uses sp_transactions to determine the name of a one-phase commit transaction that did not heuristically commit because it was not in a “prepared” state. The example then explains how to use the 1pc parameter to successfully commit the transaction:
sp_transactions
xactkey type coordinator starttime state connection dbid spid loid failover srvnname namelen xactname ------------------------------ ------- ----------- --------- ------------- ---------- ---- ---- ---- -------- --------- -------- ------------- 0xbc0500000b00000030c316480100 External XA Feb 2 2004 1:07PM Done-Detached Detached 1 0 2099 Resident Tx NULL 88 28_u7dAc31Wc380000000000000000000000000000000001HFpfSxkDM000FU_00003M00 00Y_:SYBBEV0A_LRM (1 row affected) (return status = 0)
If you try to commit this transaction, Adaptive Server issues an error message:
dbcc complete_xact ("28_u7dAc31Wc380000000000000000000000000000000001HFpfSxkDM000FU_00003M0000Y_:SYBBEV0A_LRM", "commit"))
The error message Adaptive Server issues:
Msg 3947, Level 16, State 1: Server 'PISSARRO_1251_P', Line 1: A heuristic completion related operation failed. Please see errorlog for more details. DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Because the transaction is in a “done” state, you can use a one-phase commit protocol optimization to heuristically complete the transaction after verifying the transaction was committed. You can commit this transaction using the dbcc complete_xact (“1pc”) parameter:
dbcc complete_xact ("28_u7dAc31Wc380000000000000000000000000000000001HFpfSxkDM000FU_00003M0000Y_:SYBBEV0A_LRM", "commit", "1pc")
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
You can remove the transaction from systransactions with the dbcc forget_xact command:
dbcc forget_xact ("28_u7dAc31Wc380000000000000000000000000000000001HFpfSxkDM0
00FU_00003M0000Y_:SYBBEV0A_LRM")
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
If you run sp_transactions again, the previous transaction does not appear:
sp_transactions
xactkey type coordinator starttime state connection dbid spid loid failover srvnname namelen xactname -------- ----- ------------ ---------- ------ ----------- ----- ----- ------ --------- --------- -------- -------- (0 row affected)
Enables trace flag 3604:
dbcc nodetraceoff(3604)
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Sets the dbcc scope to the cluster:
dbcc set_scope_in_cluster('cluster')
Sets the dbcc scope to the instance:
dbcc set_scope_in_cluster('instance')
Displays the current scope for dbcc commands:
dbcc set_scope_in_cluster('scope')
dbcc checkstorage reports a soft fault if any data page that is not the first data page is empty for non-hashed tables. However, dbcc checkstorage does not report this soft fault for the hashed region of a virtually-hashed table. Any data page in the hashed region of a virtually-hashed table can be empty.
You can run dbcc while the database is active, except for the dbrepair (database_name, dropdb) option and dbcc checkalloc with the fix option.
dbcc locks database objects as it checks them. For information on minimizing performance problems while using dbcc, see the dbcc discussion in the System Administration Guide.
When dbcc commands are executing, others cannot access an archive database. If you attempt to access an archive database while dbcc commands are being performed, you receive a message saying that the database is in single-user mode.
Most dbcc commands work with in-memory and relaxed durability databases.
You can use variants of the dbcc commands on an archive database that is online or offline. However, you can use a dbcc command with a fix option only on an archive database that is online.
To qualify a table or an index name with a user name or database name, enclose the qualified name in single or double quotation marks. For example:
dbcc tablealloc ("pubs2.pogo.testtable")
You cannot run dbcc reindex within a user-defined transaction.
dbcc fix_text can generate a large number of log records, which may fill up the transaction log. dbcc fix_text is designed so that updates are performed in a series of small transactions: in case of a log space failure, only a small amount of work is lost. If you run out of log space, clear your log and restart dbcc fix_text using the same table that was being upgraded when the original dbcc fix_text failed.
If you are using a replicated database, use dbcc dbrepair to load a dump from a previous release of Adaptive Server to a more current version. For example:
Loading a dump from a production system of an earlier release of Adaptive Server into a test system of the current release Adaptive Server, or
In a warm standby application, initializing a standby database of the current release of Adaptive Server with a database dump from an active database of an earlier release of Adaptive Server.
If you attempt to use select, readtext, or writetext on text values after changing to a multibyte character set, and you have not run dbcc fix_text, the command fails, and an error message instructs you to run dbcc fix_text on the table. However, you can delete text rows after changing character sets without running dbcc fix_text.
dbcc output is sent as messages or errors, rather than as result rows. Client programs and scripts should check the appropriate error handlers.
If a table is partitioned, dbcc checktable returns information about each partition.
text and image data that has been upgraded to Adaptive Server version 12.x is not automatically upgraded to the new storage format. To improve query performance and enable prefetch for this data, use the rebuild_text keyword against the upgraded text and image columns.
The amount of stack memory used in the past is only an indication of possible needs in the future. Adaptive Server may require more stack memory than it used in the past. Run dbcc stackused periodically to find your current stack memory usage.
dbcc upgrade_object check is used to detect syscomments text corruption caused by Adaptive Server defects that occurred before the upgrade. This syscomments text corruption is serious because it causes upgrade to fail.
If any error is reported by dbcc upgrade_object check, you must drop and re-create the compiled_object.
dbcc complete_xact enables a System Administrator to commit or roll back a distributed transaction in circumstances where the external transaction coordinator cannot. In versions of Adaptive Server earlier than 15.0, a transaction could not heuristically committed unless it was in the “prepare” state, and the transaction coordinator used a two-phase commit protocol to commit the transaction. However, in some cases, a transaction coordinator may want to use a one-phase commit protocol as an optimization to commit the transaction.
1pc heuristically completes a transaction that was subject to a one-phase commit protocol optimization—instead of the regular two-phase commit protocol—by the external transaction manager that was coordinating its completion. Heuristically committing such a transaction requires that the transaction be in a “done” state (as reported by sp_transactions).
Before heuristically completing the transaction, the
System Administrator should make every effort to determine whether
the coordinating transaction manager committed or rolled back the
distributed transaction.
dbcc checkcatalog checks that:
For each row in sysindexes that maps to a range, hash, or list partitioned table, there exists one row in sysobjects where sysindexes.conditionid equals sysobjects.id. dbcc checkcatalog also performs this check for each row in sysindexes that maps to a round-robin partitioned table that has a partition condition.
For each row in sysindexes that maps to a range, hash, or list partitioned table, there exists one or more rows in sysprocedures where sysindexes.conditionid equals sysprocedures.id. dbcc checkcatalog also performs this check for each row in sysindexes that maps to a round-robin partitioned table that has a partition condition
For each row in sysindexes that maps to a range, hash, or list partitioned table, there exists one row in syspartitionkeys where the following conditions are true: sysindexes.id equals syspartitionkeys.id and sysindexes.indid equals syspartitionkeys.indid. dbcc checkcatalog also performs this check for each row in sysindexes that maps to a round-robin partitioned table that has a partition condition
For each row in sysindexes, there exists one or more rows in syspartitions where both of the following conditions are true: sysindexes.id equals syspartitions.id and sysindexes.indid equals syspartitions.indid
For each row in sysobjects where type is N, there exists one row in sysindexes where sysindexes.conditionid equals sysobjects.id
For each row in syspartitions, there exists a row in sysindexes where the following conditions are true: syspartitions.id equals sysindexes.id and syspartitions.indid equals sysindexes.indid
For each row in syspartitionkeys, there exists a row in sysindexes where the following conditions are true: syspartitionkeys.id equals sysindexes.id and syspartitionkeys.indid equals sysindexes.indid
For each row in syspartitions, there exists one row in syssegments where the following condition is true: syspartitions.segments equals syssegments.segment
For each row in systabstats, there exists a row in syspartitions where the following conditions are true: syspartitions.id equals systabstats.id, syspartitions.indid equals systabstats.indid and syspartitions.partitionid equals systabstats.partitionid.
Text indices (indid=255) do not have entries in systabstats
For each row in sysstatistics, there exists a row in sysobjects where the following condition is true: sysstatistics.id equals sysobjects.id
For each encryption key row in sysobjects, Adaptive Server checks sysencryptkeys for a row defining that key.
For each column in syscolumns marked for encryption, Adaptive Server verifies that a key in sysobjects and sysencryptkeys exists
For encryption, dbcc checkcatalog includes these consistency checks:
For each encryption key row in sysobjects, sysencryptkeys is checked for the existence of a row defining that key.
For each column in syscolumns marked for encryption, the existence of the key is checked in sysobjects and sysencryptkeys.
dbcc checkcatalog ensures that:
The corresponding base key is present in sysencryptkeys for every key copy in sysencryptkeys. If the base key is not present, Adaptive Server issues an error.
For every key copy, the corresponding uid is present in sysusers. If the uid is not present, Adaptive Server issues an error.
For every decrypt default defined on a column, that the corresponding decrypt default is present in sysobjects and sysattributes. If the corresponding decrypt default is not present, Adaptive Server issues an error.
If the log segment is on its own device, running dbcc checktable on the syslogs table reports the logs used and free space. For example:
Checking syslogs The total number of data pages in this 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.4 Mbytes, 99.87%.DBCC execution completed. If dbcc printed error messages, see your System Administrator.
If the log segment is not on its own device, the following message appears:
*** NOTICE: Notification of log space used/free cannot be reported because the log segment is not on its own device.
In addition to the regular checks it performs, checktable verifies that the preallocation performed during table creation is correct:
The number of pages preallocated matches the total number of data pages that must be allocated for the specified max hash key value.
The data pages are not preallocated in an extent where the preallocation scheme specifies that only OAM pages are allowed.
The OAM pages are allocated only in the first extent of an allocation unit (AU).
dbcc traceon and dbcc traceoff apply trace flags for the entire cluster, while dbcc nodetraceoff and dbcc nodetraceon apply trace flags locally.
Although you issue dbcc quorum from an instance, the output goes to the:
Terminal that started ASE by default
Client session if trace flag 3604 is on
Client session if trace flag 3605 is on.
dbcc quorum accepts an integer parameter for the number of view records to print. For example this prints the 20 most recent view records:
dbcc quorum(20)
If you do not include a parameter dbcc quorum prints the 10 most recent view records. Pass -1 to print all view records.
Issue dbcc quorum (-1) to view all records.
You cannot include instance-only named caches with dbcc checkstorage. dbcc checkstorage issues this error message if you include an instance-only named cache:
The cache %1! cannot be used because it is an instance only cache
To run dbcc checkstorage against a local temporary database, you must run the command from the same instance that owns the local temporary database.
Compared to other releases, dbcc checkstorage for the Cluster Edition may report more soft faults when users in multiple instances update data. For performance reasons, dbcc checkstorage may not query the latest version of a page in the cluster, causing it to report more soft faults.
For well-partitioned applications where a single instnace updates a database, dbcc checkstorage behaves the same as earlier Adaptive Server releases.
ANSI SQL – Compliance level: Transact-SQL extension.
Table owner Only the table owner can execute dbcc with checktable, fix_text, rebuild_text, or reindex.
Database Owner Only the Database Owner can use checkstorage, checkdb, checkcatalog, checkalloc, indexalloc, and tablealloc.
System Administrator Only a System Administrator can use dbrepair, complete_xact, engine, and forget_xact, stackused, traceoff, traceon, and quorum.
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
81 |
dbcc |
dbcc |
|
Commands drop database
System procedures sp_configure, sp_helpdb
