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

addtempdb

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.

dbid

is the database ID.

database_name

is the name of the database to check. If no database name is given, dbcc uses the current database.

checkalloc

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.

fix | nofix

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 details on page allocation in Adaptive Server, see the System Administration Guide.

checkcatalog

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

fix

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 Adaptive Servers earlier than version 12.5.2.

checkdb

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.

skip_ncindex

causes dbcc checktable or dbcc checkdb to skip checking the nonclustered indexes on user tables. The default is to check all indexes.

checkindex

runs the same checks as checktable, but only on the specified index. You can use checkindex in an archive database.

bottom_up

(Data-only-locked tables only) Checks indexes in a bottom-up order when specifying this option with checkindex. The bottom_up check involves verifying whether each data row has a corresponding index row.

partition_name | partition_id

is the name or ID of the data partition to check. If you specify a partition, dbcc skips global indexes.

checkstorage

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.

checktable

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.

table_name | table_id

is the name or object ID of the table to check.

fix_spacebits

is for tables that use datapages or datarows locking, 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.

check spacebits

checks space bits for tables that use datapages or datarows locking tables. If you specify check spacebits, dbcc does not check nonclustered indexes.

checkverify

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.

ignore_exclusions

enables or disables the exclusion list. Value is either 0, the default (enables the exclusion list), or 1 (disables the exclusion list).

complete_xact

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.

xid

is a transaction name from the systransactions.xactname column. You can also determine valid xid values using sp_transactions.

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. This option allows the heuristic commit of a transaction that was not in the prepared state.

forget_xact

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.

dbrepair (database_name, dropdb)

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

engine

takes Adaptive Server engines offline or brings them online. If enginenum is not specified, dbcc engine (offline) takes the highest-numbered engine offline. See “Managing Multiprocessor Servers,” in the System Administration Guide.

fix_text

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. See the System Administration Guide.

indexalloc

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.

NoteYou can specify fix or nofix only if you include a value for the type of report (full, optimized, fast, or null).

table_name | table_id

is the table’s name or the table’s object ID.

indid

is the ID of the index that is checked during dbcc indexalloc.

fix_spacebits

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.

check spacebits

checks space bits for datapages or datarows locked tables. If you specify check spacebits, dbcc does not check nonclustered indexes.

full

reports all types of allocation errors.

optimized

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.

fast

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

fix | nofix

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

partition_name | partition_id

if you specify a partition ID, allocation checks are performed on the partition identified by (indid, partition id).

pravailabletempdbs

prints the global list of available temporary databases.

rebuild_text

rebuilds or creates an internal Adaptive Server 12.0 or later 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.

table_name | table_id | "all"

is the table’s name or the table’s object ID, or all the objects in the database.

column

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.

text_page

is the logical page number of the first text page. dbcc rebuild_text rebuilds the internal data structure of this text page.

data_partition_name | data_partition_id

is name or ID of the data partition. If you specify text_page, data_partition_name (or data_partition_id) is ignored.

monitor increment, group name

The increment and decrement commands increase and decrease, by 1, the usage counts for the monitor counters in the specified group. The reset command sets the usage count for the monitor counters in the specified group to zero. This turns 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.

reindex

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.

stackused

reports the maximum amount of stack memory used since the server first started.

serverlimits

displays the limits Adaptive Server enforces on various entities, including 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 information to determine the various sizing characteristics of the Adaptive Server process.

tablealloc

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.

textalloc

checks the allocation integrity of text or image pages in a database. You can use dbcc textalloc with an archive database.

full

is equivalent to checkalloc at a table level; it reports all types of allocation errors.

optimized

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.

fast

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

fix | nofix

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

data_partition_name | data_partition_id

is name or ID of the data partition to check. If you specify a partition, dbcc tablealloc skips global indexes.

traceon | traceoff

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.

tune

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.

upgrade_object

upgrades a compiled object from the text stored in the syscomments table. The upgrade_object parameters are:

Parameters

Description

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.

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.

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.

check

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.

force

forces an upgrade of the object from syscomments even if an upgrade is not required.

object_name

is the name of the compiled object.

object_type

is one of the following object types that Adaptive Server compiles: procedure, function, view, trigger, default, rule, condition.

compiled_object_name

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.

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.

trace_flag_number

is the number of the trace flag you are enabling or disabling.

cluster

sets the dbcc command scope to the cluster. Subsequent dbcc commands have a cluster-wide effect.

instance

sets the dbcc command scope to the current instance. Subsequent dbcc commands affect only the local instance.

scope

displays the current scope of the dbcc command, either cluster or instance.

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

NoteTo show a complete listing of limits in the server, execute dbcc traceon (3604) to get the output to the client session.

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 complete_xact

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

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


Checking performed by dbcc checkcatalog

dbcc checkcatalog checks that:


Using dbcc checktable

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:


Using dbcc nodetraceoff and dbcc nodetraceon (clusters only)

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


Using dbcc quorum (clusters only)


Restrictions on dbcc checkstorage for shared-disk clusters:

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for dbcc commands differ based on your granular permissions settings. See Table 1-17 for permission requirement for each dbcc command.

NoteYou can run a dbcc command if you have any one of the requirements (privileges or ownership) listed in the table for that command.

Table 1-17: Permissions requirement for dbcc command

DBCC command name

Permission Requirement

Granular permissions disabled

Granular permissions enabled

addtempdb

  • database owner

  • sa_role

  • database owner

  • own database

checkalloc

  • dbcc checkalloc

  • database owner

  • sa_role

  • dbcc checkalloc

checkcatalog

  • dbcc checkcatalog

  • database owner

  • sa_role

  • dbcc checkcatalog

checkdb

  • dbcc checkdb

  • database owner

  • sa_role

  • dbcc checkdb

checkindex

  • dbcc checkindex

  • table owner

  • sa_role

  • dbcc checkindex

  • table owner

checkstorage

  • dbcc checkstorage

  • database owner

  • sa_role

  • dbcc checkstorage

checktable

  • dbcc checktable

  • table owner

  • sa_role

  • dbcc checktable

  • table owner

checkverify

  • dbcc checkverify

  • database owner

  • sa_role

  • dbcc checkverify

complete_xact

  • sa_role

  • manage server

dbrepair dropdb

  • database owner

  • sa_role

  • database owner

  • own database

engine

  • sa_role

  • manage server

fix_text

  • dbcc fix_text

  • object owner

  • sa_role

  • dbcc fix_text

  • object owner

forget_xact

  • sa_role

  • manage server

indexalloc

  • dbcc indexalloc

  • table owner

  • sa_role

  • dbcc indexalloc

  • table owner

monitor

  • sa_role

  • manage server

rebuild_text

  • table owner

  • sa_role

  • table owner

  • manage database

reindex

  • dbcc reindex

  • table owner

  • sa_role

  • dbcc reindex

  • table owner

stackused

  • sa_role

  • manager server

tablealloc

  • dbcc tablealloc

  • table owner

  • sa_role

  • dbcc tablealloc

  • table owner

textalloc

  • dbcc textalloc

  • table owner

  • sa_role

  • dbcc textalloc

  • table owner

traceon | traceoff

nodetraceon | nodetraceoff

     trace flags – 3604, 3605

  • sa_role

  • set tracing

  • monitor qp performance

  • set switch

     all other trace flags

  • sa_role

  • set switch

tune ascinserts

  • dbcc tune

  • table owner

  • sa_role

  • dbcc tune

  • table owner

tune all other parameters

  • dbcc tune

  • sa_role

  • dbcc tune

upgrade_object

  • object owner

  • database owner

  • sa_role

  • manage database

  • object owner

set_scope_in_cluster

  • sa_role

  • manage cluster

quorum

  • sa_role

  • manage cluster

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

81

dbcc

dbcc

  • Roles – current active roles

  • Keywords or options – Any of the dbcc keywords such as checkstorage and the options for that keyword

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

See also

Commands drop database

System procedures sp_configure, sp_helpdb