Appendix C -- DBCC Commands

3 4

Within Microsoft SQL Server is a set of commands designed to assess and correct consistency problems in the SQL Server database. When you run one of these commands, you use the string DBCC as a prefix. In fact, "DBCC" stands for "database consistency checker." Although DBCC commands were originally designed to be used as consistency-checking tools, the scope of the DBCC commands has expanded over the years.

This appendix lists the DBCC commands and provides a brief description for each one, allowing you to quickly find the command that you need. This appendix does not show all of the options for each DBCC command. You'll find complete instructions for running these commands in SQL Server Books Online.

  • CHECKALLOC Checks the allocation and use of all of the pages in the specified database. CHECKALLOC is a subset of CHECKDB.
  • CHECKCATALOG Checks for consistency within the system tables for the specified database.
  • CHECKCONSTRAINTS Checks the integrity of a single constraint or of all constraints on a table.
  • CHECKDB Checks the allocation (of rows, pages, and extents) and structural integrity of all the objects in the specified database. This command catches and repairs problems with the database allocation and tables. In fact, DBCC CHECKDB validates the integrity of everything within the database. Expect it to take some time to run.
  • CHECKFILEGROUP Checks the allocation and structural integrity of objects in a specific filegroup in the database. You can use this command rather than DBCC CHECKDB if you suspect that only a filegroup has been corrupted, which can occur because of a hardware failure.
  • CHECKIDENT Checks and, if necessary, corrects the current identity value for the specified table.
  • CHECKTABLE Checks the integrity of the data, index, text, ntext, and image pages for the specified table. This command is useful for checking a table you suspect might be corrupted.
  • CLEANTABLE Reclaims space that was formerly used for variable-length varchar data as well as text data.
  • CONCURRENCYVIOLATION For SQL Server Standard Edition and Personal Edition, reports when more than five concurrent SQL batches are being executed on the server.
  • DBREINDEX Was used to reindex a table's indexes. This command is no longer supported in SQL Server 2000. With Microsoft SQL Server 2000, use the DROP EXISTING clause with the CREATE INDEX command to accomplish this task.
  • DBREPAIR Was used to drop a damaged database. This command is not supported in SQL Server 2000. You should use the DROP DATABASE command instead.
  • dllname (FREE) Unloads the specified extended stored procedure DLL from memory.
  • DROPCLEANBUFFERS Removes all clean buffers from the buffer pool.
  • FREEPROCCACHE Removes all elements from the procedure cache.
  • HELP Returns the syntax of a specific DBCC statement, thus allowing you to quickly use that command without having to refer to SQL Server Books Online. Do not include the DBCC keyword when you specify the statement name.
  • INDEXDEFRAG Defragments clustered and secondary indexes for the specified table.
  • INPUTBUFFER Displays the last statement sent by the user associated with the specified system process ID (SPID) to SQL Server.
  • MEMUSAGE Provided a detailed report about memory usage. This command is no longer supported.
  • NEWALLOC Provided the same functionality as DBCC CHECKALLOC does. This command is no longer supported.
  • OPENTRAN Displays information about the oldest active transaction in the database. This is useful for finding stalled or long-running transactions.
  • OUTPUTBUFFER Displays the output data sent by SQL Server to the user associated with the specified SPID.
  • PINTABLE Marks the table to be pinned. A pinned table does not relinquish itself from the cache. In other words, it is permanently placed in the cache and does not get ejected. This is useful for small tables that are infrequently used but that require immediate access. You should ensure the pinned table does not use enough memory to adversely affect other SQL Server processing.
  • PROCCACHE Displays information about the SQL Server procedure cache. This information can be valuable in assessing the effectiveness of the procedure cache and SQL statements.
  • ROWLOCK Was used in Microsoft SQL Server 6.5 in order to enable row locking. This is automatic in SQL Server 2000.
  • SHOWCONTIG Displays information about the fragmentation of the data and indexes of the specified table. A heavily fragmented index should be rebuilt. A heavily fragmented table should be exported and imported back into the database.
  • SHOW_STATISTICS Displays statistics for the specified target on the specified table. These are the statistics that are used by SQL Query Optimizer.
  • SHRINKDATABASE Shrinks the size of the files associated with the specified database. The recommended method of performing this task is using the autoshrink feature of sp_dboption.
  • SHRINKFILE Shrinks the size of the specified file. This file can be a data file or a log file.
  • SQLPERF Provides information about the transaction log space usage in all databases. Using this command to track log space usage is helpful for capacity planning and sizing.
  • TEXTALL Was used to check the consistency of tables that contain text, ntext, and image columns. In SQL Server 2000, DBCC CHECKDB and CKECKTABLE perform this task. However, DBCC CHECKDB is the recommended replacement for DBCC TEXTALL.
  • TEXTALLOC Was used to check the consistency of tables that contain text, ntext, and image columns. In SQL Server 2000, DBCC CHECKDB and CHECKTABLE perform this task. However, DBCC CHECKTABLE is the recommended replacement for DBCC TEXTALLOC. DBCC TEXTALL previously ran DBCC TEXTALLOC.
  • TRACEOFF Disables the specified SQL Server trace flag or flags.
  • TRACEON Enables the specified SQL Server trace flag or flags.
  • TRACESTATUS Displays the status of the specified trace flag or flags.
  • UNPINTABLE Marks a previously pinned table to be unpinned. The table is subsequently treated in the cache as any other object.
  • UPDATEUSAGE Reports and corrects inaccuracies in the sysindexes table. These inaccuracies might cause sp_spaceused to return incorrect data.
  • USEROPTIONS Returns the status of the SET statement operations that are currently set for the current connection. This works for only the current connection.


Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net