dbcc

Description

Database consistency checker (dbcc) checks the logical and physical consistency of a database and provides statistics, planning, and repair functionality.

Certain dbcc commands apply only to shared-disk clusters. See the separately listed dbcc syntax for clusters.

Syntax

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 dbrepair (database_name, dropdb)

dbcc engine ({offline, [enginenum] | "online"})

dbcc fix_text ({table_name | table_id})

dbcc forget_xact (xid)

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" | 
		des_bind, dbid, object_name
		des_unbind, dbid, object_name
		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

Parameters

Examples

Example 1

Checks pubs2 for page allocation errors:

dbcc checkalloc (pubs2)

Example 2

Checks database consistency for pubs2 and places the information in the dbccdb database:

dbcc checkstorage (pubs2)

Example 3

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.

Example 4

Heuristically aborts the transaction “distributedxact1:”

dbcc complete_xact (distributedxact1, "rollback")

Example 5

Upgrades text values for blurbs after a character set change:

dbcc fix_text (blurbs)

Example 6

Runs checkverify on the table tab, with exclusion list disabled, in the database my_db:

dbcc checkverify(my_db, tab)

Example 7

Runs dbcc checkverify on table tab, in database my_db, with the exclusion list enabled:

dbcc checkverify (my_db, tab, 0)

Example 8

Runs dbcc checkverify on table tab, in database my_db, with the exclusion list disabled, enter:

dbcc checkverify (my_db, tab, 1)

Example 9

Removes information for the transaction “distributedxact1” from master.dbo.systransactions:

dbcc forget_xact (distributedxact1)

Example 10

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)

Example 11

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.

Example 12

Rebuilds or creates an internal Adaptive Server data structure for all text and image columns in the blurbs table:

dbcc rebuild_text (blurbs)

Example 13

Checks part of the titles table that resides on the smallsales partition (which contains all booksales less than 5000)

dbcc checktable (titles, NULL, "smallsales")

Example 14

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.

Example 15

Checks the maximum amount of stack memory used since Adaptive Server started:

dbcc stackused

Example 16

Upgrades all stored procedures in the listdb database:

dbcc upgrade_object(listdb, 'procedure')

Example 17

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)

Example 18

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 
...

Example 19

Returns an optimized report of allocation for this table, but does not fix any allocation errors:

dbcc tablealloc (publishers, null, nofix)

Example 20

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)

Example 21

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)

Example 22

Enables trace flag 3604:

dbcc nodetraceoff(3604)

DBCC execution completed. If DBCC printed error messages, contact a user with system administrator (SA) role.

Example 23

Sets the dbcc scope to the cluster:

dbcc set_scope_in_cluster('cluster')

Example 24

Sets the dbcc scope to the instance:

dbcc set_scope_in_cluster('instance')

Example 25

Displays the current scope for dbcc commands:

dbcc set_scope_in_cluster('scope')

Usage

• dbcc checkstorage reports a soft fault if any data page that is not the first data page is empty for nonhashed 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, users 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 dbcc with a fix option only on an online archive database.

• 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 and need to load a dump from an earlierversion of Adaptive Server to the current version, use dbcc dbrepair. For example:

  • Load a dump from a production system of the earlier version of Adaptive Server into a test system of the current version Adaptive Server, or

  • In a warm standby application, initialize a standby database of the current version of Adaptive Server with a database dump from an active database of the earlier version 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 or later is not automatically upgraded from its original 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 future needs. Adaptive Server may require more stack memory than it used in the past. Periodically run dbcc stackused 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 you upgraded Adaptive Server. This syscomments text corruption is serious because it causes the upgrade process 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).

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 indexes (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.

• 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 object allocation map (OAM) pages are allowed.

• The OAM pages are allocated only in the first extent of an allocation unit.

dbcc traceon and dbcc traceoff apply trace flags for the entire cluster, while dbcc nodetraceoff and dbcc nodetraceon apply trace flags locally.

• dbcc quorum output goes to:

  • By default, terminal that started Adaptive Server

  • The client session if trace flag 3604 or 3605 is on

• dbcc quorum accepts an integer parameter for the number of view records to print. For example, to print the 20 most recently view records, use:

  dbcc quorum(20)
  

• If you do not include a parameter dbcc quorum prints the 10 most recent 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 do so:

  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.

• For performance reasons, dbcc checkstorage in the Cluster Edition may not query the latest version of a page in the cluster. This may cause the Cluster Edition to report more soft faults than other versions.

  For well-partitioned applications where a single instance updates a database, dbcc checkstorage behaves as earlier non-Cluster Edition Adaptive Server versions.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Auditing

Values in event and extrainfo columns of sysaudits are:

See also