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 | databbase_name )
dbcc checkalloc [(database_name [, fix | nofix])]
dbcc checkcatalog [(database_name)]
dbcc checkdb [(database_name [, skip_ncindex])]
dbcc checkstorage [(database_name)]
dbcc checktable({table_name | table_id}[, skip_ncindex])
dbcc checkverify [(database_name)]
dbcc complete_xact (xid, {"commit" | "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 [, {full | optimized | fast | null}
[, fix | nofix]])
dbcc pravailabletempdbs
dbcc rebuild_text (table [, column
[, text_page_number]])
dbcc reindex ({table_name | table_id})
dbcc tablealloc ({table_name | table_id}
[, {full | optimized | fast | null}
[, fix | nofix]])|
dbcc { traceon | traceoff } (flag [, flag ... ])
dbcc tune ( { ascinserts, {0 | 1 } , tablename |
cleanup, {0 | 1 } |
cpuaffinity, start_cpu {, on| off } |
des_greedyalloc, dbid, object_name,
" { on | off }" |
deviochar vdevno, "batch_size" |
doneinproc { 0 | 1 }})
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. checkcatalog also reports on any segments that have been defined. If no database name is given, checkcatalog checks the current database.
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.
checkstorage
checks the specified database for allocation, 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 log(s) 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.
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.
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.
forget_xact
removes the commit 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 will not be available to clear commit status in systransactions.
Warning!
Never 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.
xid
is a transaction name from the systransactions.xactname column. You can also determine valid xid values using sp_transactions.
dbrepair (database_name, dropdb)
drops a damaged database. drop database does not work on a damaged database.
Users cannot be using the database being dropped when this dbcc statement is issued (including the user issuing the statement).
fengine
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's 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.
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, index_id
is the table name or the table's object ID (the id column from sysobjects) plus the index's indid from sysindexes.
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).
pravailabletempdbs
prints the global list of available temporary databases.
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).
rebuild_text
rebuilds or creates an internal Adaptive Server 12.x data structure for text or image data. This data structure enables Adaptive Server to perform random access and asynchronous prefetch during data queries.
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 Adaptive Server's 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.
tablealloc
checks the specified table 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).
traceon | traceoff
toggles the printing of diagnostics during query optimization (flag values 302, 310, and 317). Values 3604 and 3605 toggle sending trace output to the user session and to the error log, respectively. For more information, see Chapter 37, "Tuning with dbcc traceon" in the Performance and Tuning Guide.
tune
enables or disables tuning flags for special performance situations. For more information on the individual options, see the Performance and Tuning Guide.
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 12.x data structure for all text and image columns in the blurbs table:
dbcc rebuild_text (blurbs)
Example 10
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 11
Adaptive Server returns an optimized report of allocation for this table, but does not fix any allocation errors:
dbcc tablealloc(publishers, null, nofix)
Usage
• dbcc, the Database Consistency Checker, can be run 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.
• 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")
• dbcc reindex cannot be run 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 done 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 attempt to use select, readtext, or writetext on text values after changing to a multibyte character set, and you have not run dbcc fix_text, the command fails, and an error message instructs you to run dbcc fix_text on the table. However, you can delete text rows after changing character sets without running dbcc fix_text.
• dbcc output is sent as messages or errors, rather than as result rows. Client programs and scripts should check the appropriate error handlers.
• If a table is partitioned, dbcc checktable returns information about each partition.
• text and image data that has been upgraded to Adaptive Server version 12.x is not automatically upgraded to the new storage format. To improve query performance and enable prefetch for this data, use the rebuild_text keyword against the upgraded text and image columns.
Standards
ANSI SQL - Compliance level: Transact-SQL extension.
Permissions
Only the table owner can execute dbcc with the checktable, fix_text, rebuild_text, or reindex keywords.
Only the Database Owner can use the checkstorage, checkdb, checkcatalog, checkalloc, indexalloc, and tablealloc keywords.
Only a System Administrator can use the dbrepair, complete_xact, and forget_xact keywords.
Only a System Administrator can use dbcc traceon and dbcc traceoff commands.
Only a System Administrator can use dbcc engine.
See also
Commands
drop database
System procedures
sp_configure, sp_helpdb
The following describes the types of table locks.
•Intent lock
An intent lock indicates that page-level or row-level locks are currently held on a table. Adaptive Server applies an intent table lock with each shared or exclusive page or row lock, so an intent lock can be either an exclusive lock or a shared lock. Setting an intent lock prevents other transactions from subsequently acquiring conflicting table-level locks on the table that contains that locked page. An intent lock is held as long as page or row locks are in effect for the transaction.
•Shared lock
This lock is similar to a shared page or lock, except that it affects the entire table. For example, Adaptive Server applies a shared table lock for a select command with a holdlock clause if the command does not use an index. A create nonclustered index command also acquires a shared table lock.
•Exclusive lock
This lock is similar to an exclusive page or row lock, except it affects the entire table. For example, Adaptive Server applies an exclusive table lock during a create clustered index command. update and delete statements require exclusive table locks if their search arguments do not reference indexed columns of the object.
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 | databbase_name )
dbcc checkalloc [(database_name [, fix | nofix])]
dbcc checkcatalog [(database_name)]
dbcc checkdb [(database_name [, skip_ncindex])]
dbcc checkstorage [(database_name)]
dbcc checktable({table_name | table_id}[, skip_ncindex])
dbcc checkverify [(database_name)]
dbcc complete_xact (xid, {"commit" | "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 [, {full | optimized | fast | null}
[, fix | nofix]])
dbcc pravailabletempdbs
dbcc rebuild_text (table [, column
[, text_page_number]])
dbcc reindex ({table_name | table_id})
dbcc tablealloc ({table_name | table_id}
[, {full | optimized | fast | null}
[, fix | nofix]])|
dbcc { traceon | traceoff } (flag [, flag ... ])
dbcc tune ( { ascinserts, {0 | 1 } , tablename |
cleanup, {0 | 1 } |
cpuaffinity, start_cpu {, on| off } |
des_greedyalloc, dbid, object_name,
" { on | off }" |
deviochar vdevno, "batch_size" |
doneinproc { 0 | 1 }})
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. checkcatalog also reports on any segments that have been defined. If no database name is given, checkcatalog checks the current database.
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.
checkstorage
checks the specified database for allocation, 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 log(s) 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.
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.
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.
forget_xact
removes the commit 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 will not be available to clear commit status in systransactions.
Warning!
Never 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.
xid
is a transaction name from the systransactions.xactname column. You can also determine valid xid values using sp_transactions.
dbrepair (database_name, dropdb)
drops a damaged database. drop database does not work on a damaged database.
Users cannot be using the database being dropped when this dbcc statement is issued (including the user issuing the statement).
fengine
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's 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.
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, index_id
is the table name or the table's object ID (the id column from sysobjects) plus the index's indid from sysindexes.
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).
pravailabletempdbs
prints the global list of available temporary databases.
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).
rebuild_text
rebuilds or creates an internal Adaptive Server 12.x data structure for text or image data. This data structure enables Adaptive Server to perform random access and asynchronous prefetch during data queries.
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 Adaptive Server's 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.
tablealloc
checks the specified table 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).
traceon | traceoff
toggles the printing of diagnostics during query optimization (flag values 302, 310, and 317). Values 3604 and 3605 toggle sending trace output to the user session and to the error log, respectively. For more information, see Chapter 37, "Tuning with dbcc traceon" in the Performance and Tuning Guide.
tune
enables or disables tuning flags for special performance situations. For more information on the individual options, see the Performance and Tuning Guide.
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 12.x data structure for all text and image columns in the blurbs table:
dbcc rebuild_text (blurbs)
Example 10
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 11
Adaptive Server returns an optimized report of allocation for this table, but does not fix any allocation errors:
dbcc tablealloc(publishers, null, nofix)
Usage
• dbcc, the Database Consistency Checker, can be run 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.
• 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")
• dbcc reindex cannot be run 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 done 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 attempt to use select, readtext, or writetext on text values after changing to a multibyte character set, and you have not run dbcc fix_text, the command fails, and an error message instructs you to run dbcc fix_text on the table. However, you can delete text rows after changing character sets without running dbcc fix_text.
• dbcc output is sent as messages or errors, rather than as result rows. Client programs and scripts should check the appropriate error handlers.
• If a table is partitioned, dbcc checktable returns information about each partition.
• text and image data that has been upgraded to Adaptive Server version 12.x is not automatically upgraded to the new storage format. To improve query performance and enable prefetch for this data, use the rebuild_text keyword against the upgraded text and image columns.
Standards
ANSI SQL - Compliance level: Transact-SQL extension.
Permissions
Only the table owner can execute dbcc with the checktable, fix_text, rebuild_text, or reindex keywords.
Only the Database Owner can use the checkstorage, checkdb, checkcatalog, checkalloc, indexalloc, and tablealloc keywords.
Only a System Administrator can use the dbrepair, complete_xact, and forget_xact keywords.
Only a System Administrator can use dbcc traceon and dbcc traceoff commands.
Only a System Administrator can use dbcc engine.
See also
Commands
drop database
System procedures
sp_configure, sp_helpdb
The following describes the types of table locks.
•Intent lock
An intent lock indicates that page-level or row-level locks are currently held on a table. Adaptive Server applies an intent table lock with each shared or exclusive page or row lock, so an intent lock can be either an exclusive lock or a shared lock. Setting an intent lock prevents other transactions from subsequently acquiring conflicting table-level locks on the table that contains that locked page. An intent lock is held as long as page or row locks are in effect for the transaction.
•Shared lock
This lock is similar to a shared page or lock, except that it affects the entire table. For example, Adaptive Server applies a shared table lock for a select command with a holdlock clause if the command does not use an index. A create nonclustered index command also acquires a shared table lock.
•Exclusive lock
This lock is similar to an exclusive page or row lock, except it affects the entire table. For example, Adaptive Server applies an exclusive table lock during a create clustered index command. update and delete statements require exclusive table locks if their search arguments do not reference indexed columns of the object.
Comments
Post a Comment