dbcc

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, flushthreshold)

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 zapdefraginfo 

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 SAP ASE servers, 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. 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 SAP ASE 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. The SAP ASE 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 SAP ASE 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.
• 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 the SAP ASE 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 SAP ASE 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, the SAP ASE 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.

• 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 the SAP ASE server transaction coordination services automatically clear the commit status in systransactions.
• 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, the SAP ASE server uses optimized. The fix | nofix option functions the same with indexalloc as with tablealloc.

  Note: You 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).
• 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.

• pravailabletempdbs – prints the global list of available temporary databases.
• rebuild_text – rebuilds or creates an internal SAP ASE version 12.0 or later data structure for text, or unitext, image data. This data structure enables the SAP ASE 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.
• 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 SAP ASE sort order has been changed and indexes have been marked “suspect” by the SAP ASE 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 the SAP ASE server is restarted following a sort order change.

• serverlimits – displays the limits the SAP ASE 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 the SAP ASE server process.
• stackused – reports the maximum amount of stack memory used since the server first started.
• 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, the SAP ASE 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 the SAP ASE 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 is not returning 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 the SAP ASE 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 is not returning 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.
• zapdefraginfo – deletes rows that are stored in sysattributes. For every data partition undergoing incremental reorganization, a row is stored in sysattributes. Use zapdefraginfo to delete this information before performing a downgrade.

  In a running server, if the rows with defragmentation information for a specific object are accidentally lost from sysattributes, use zapdefraginfo to reset the extent version information for the specific object so that a later reorg defrag will not fail to consider all the extents of the object.

• 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 SAP ASE 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 Discoveres 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 the SAP ASE 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 SAP ASE 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 
  ...

  Note: To 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, the SAP ASE server issues an error message:

  dbcc complete_xact ("28_u7dAc31Wc380000000000000000000000000000000001HFpfSxkDM000FU_00003M0000Y_:SYBBEV0A_LRM", "commit"))

  The error message the SAP ASE 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 cannot lock a table on which you previously executed the dbcc tune(des_bind…) command because the SAP ASE server does not allow shared or exclusive table locks on hot objects. For example, the SAP ASE server issues warning number 8242 if you:

  • Create a table

  • Run dbcc tune (des_bin. . . ). For example:

    dbcc tune(des_bin, 4, new_table)

  • Attempt to lock the table:

    begin tran
    lock table new_table in exclusive mode
    go

    Msg 8242, Level 16, State 1:
    Server 'server01', Line 2:
    The table ‘new_table’ in database 'big_db' is bound to metadata cache memory. Unbind the table and retry the query later.

• 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 the SAP ASE server to the current version, use dbcc dbrepair. For example:

  • Load a dump from a production system of the earlier version of the SAP ASE server into a test system of the current version SAP ASE server, or

  • In a warm standby application, initialize a standby database of the current version of the SAP ASE server with a database dump from an active database of the earlier version of the SAP ASE 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 SAP ASE 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. The SAP ASE 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 SAP ASE defects that occurred before you upgraded the SAP ASE 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.

(Cluster Edition only) dbcc traceon and dbcc traceoff apply trace flags for the entire cluster, while dbcc nodetraceoff and dbcc nodetraceon apply trace flags locally.

See also sp_configure, sp_helpdb in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for dbcc commands differ based on your granular permissions settings. This table shows the permission requirement for each dbcc command.

Auditing

Values in event and extrainfo columns of sysaudits are: