create view  deallocate cursor

Chapter 1: Commands

dbcc

Description

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

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 forget_xact (xid)
dbcc dbrepair (database_name, dropdb)
dbcc engine( {offline , [enginenum] | "online" })
dbcc fix_text ({table_name | table_id})
dbcc indexalloc(table_name | table_id, index_id 
	[,optimized | fast | NULL [, fix | nofix | NULL 
	[, partition_name | partition_id]]])
dbcc monitor (increment, <group name>)
dbcc monitor (decrement, <group name>)
dbcc monitor (reset, <group name>)
dbcc pravailabletempdbs
dbcc rebuild_text(table_name | table_id | "all" [, column [, text_page [, data_partition_name | data_partition_id]]])
dbcc reindex ({table_name | table_id})
dbcc serverlimits
dbcc stackused
dbcc tablealloc(table_name | table_id [, full | optimized | fast | NULL 
	[, fix | nofix | NULL [, data_partition_name | data_partition_id ]]])
dbcc { traceon | traceoff } (flag [, flag ... ])
dbcc tune ( { ascinserts, {0 | 1 } , table_name |
		cleanup, {0 | 1 } | 
		cpuaffinity, start_cpu {, on| off } | 
		des_greedyalloc, dbid, object_name,
			" { on | off }" | 
		deviochar vdevno, "batch_size" | 
		doneinproc { 0 | 1 }})
dbcc upgrade_object( [dbid | dbname 
	[, object_name | object_type [, force | check])))

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 a discussion of 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. For a complete list of the checking that checkcatalog performs, see “Checking performed by dbcc 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 a 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.

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.

bottom_up

DOL tables only – checks indexes in a bottom-up order when specifying this option with checkindex. The bottom_up check involves verifying whether each datarow has a corresponding index row.

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

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

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 tables of type datapages or datarows lockscheme. If you specify check spacebits, dbcc does not check nonclustered indexes.

bottom_up

DOL tables only – checkes indexes in a bottom-up order when specifying this option with checktable.

partition_name | partition_id

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

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. For more information, see Chapter 8, “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. For more information, 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.

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 tables of type datapages or datarows lockscheme. 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.x data structure for text, or unitext, image data. This data structure enables Adaptive Server to perform random access and asynchronous prefetch during data queries.You can run rebuild_text on all tables in a database, a single table, or a data partition.

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 the usage counts for the monitor counters in the specified group by 1. The reset command sets the usage count for the monitor counters in the specified group to zero. This will turn off collection of monitoring data for this group.

group name can be one of the following:

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

display the limits Adaptive Server enforces on various entities. These include the lengths of identifiers and the maximum number of different objects such as number of columns in a table, number of indexes on a table, page sizes, row-overheads, and so on. Use the inforamtion to determine the various sizing characteristics of the Adaptive Server process.

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

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.

check

checks syntax for the specified compiled object in syscomments in the specified database. Does not raise errors on occurrences of select.

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.

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

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

dbcc forget_xact (distributedxact1)

Example 7

Adaptive Server returns a full report of allocation for the index with an indid of 2 on the titleauthor table and fixes any allocation errors:

dbcc indexalloc ("pubs..titleauthor", 2, full)

Example 8

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 9

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

dbcc rebuild_text (blurbs)

Example 10

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 11

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 12

Checks the logical and physical consistency of a database and provides statistics, planning, and repair functionality:

dbcc stackused

Example 13

Shows the output to the client session for the vairous forms of limits in your Adaptive Server (in abbreviated form for this example):

dbcc traceon(3604)
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 14

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

dbcc tablealloc(publishers, null, nofix)

Example 15

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 16

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)

Usage


Additions to 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:

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Table owner Only the table owner can execute dbcc with checktable, fix_text, rebuild_text, or reindex.

Database Owner Only the Database Owner can use checkstorage, checkdb, checkcatalog, checkalloc, indexalloc, and tablealloc.

System Administrator Only a System Administrator can use dbrepair, complete_xact, engine, and forget_xact, stackused, traceoff, and traceon.

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 in effect

See also

Commands drop database

System procedures sp_configure, sp_helpdb





Copyright © 2005. Sybase Inc. All rights reserved. deallocate cursor

View this book as PDF