DBCC Statement

Used to check the logical and physical consistency of a database, check memory usage, decrease the size of a database, check performance statistics, and so on. DBCC is the SQL Server "database consistency checker." DBCC helps ensure the physical and logical consistency of a database; however, DBCC is not corrective. It is recommended that you make periodic checks to ensure the logical and physical consistency of your data.

Syntax

DBCC {
    CHECKALLOC [(database_name [, NOINDEX])] |
    CHECKCATALOG [(database_name)] |
    CHECKTABLE (table_name [, NOINDEX | index_id]) |
    CHECKDB [(database_name [, NOINDEX])] |
    CHECKIDENT [(table_name)] |
    DBREPAIR (database_name, DROPDB [, NOINIT]) |
    dllname (FREE) |
    
INPUTBUFFER (spid) |
    MEMUSAGE |
    NEWALLOC [(database_name [, NOINDEX])] |
    OPENTRAN ({database_name} | {database_id})
        [WITH TABLERESULTS] |
    OUTPUTBUFFER (spid) |
    PERFMON |
    PINTABLE (database_id, table_id) |
    SHOW_STATISTICS (table_name, index_name) |
    SHOWCONTIG (table_id, [index_id]) |
    SHRINKDB (database_name [, new_size [, 'MASTEROVERRIDE']]) |
    SQLPERF ({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} |
        {THREADS} | {LOGSPACE}) |
    TEXTALL [({database_name | database_id}[, FULL | FAST])] |
    TEXTALLOC [({table_name | table_id}[, FULL | FAST])] |
    TRACEOFF (trace#) |
    TRACEON (trace#) |
    TRACESTATUS (trace# [, trace#...]) |
    UNPINTABLE (database_id, table_id) |
    UPDATEUSAGE ({0 | database_name} [, table_name [, index_id]]) |
    USEROPTIONS}
[WITH NO_INFOMSGS]

where

database_id
Specifies the ID of the database in which the command is to be run. If database_id is not supplied, the current database is assumed.
database_name
Specifies the database in which the command is to be run. If database_name is not supplied, the current database is assumed.
index_id
Specifies the ID of the index to be used. When optional and not specified, the statement is used against all indexes for the specified table.
index_name
Specifies the index to be used.
table_id
Specifies the ID of the table to be used. When optional and not specified, the statement is used against all tables in the current or specified database.
table_name
Specifies the table to be used.
CHECKALLOC [(database_name [, NOINDEX])]
Checks the specified database to make sure that all pages are correctly allocated and used. If no database name is given, CHECKALLOC checks the current database. CHECKALLOC reports on the amount of space allocated and used.

DBCC CHECKALLOC is provided for backward compatibility. The preferred DBCC statement is NEWALLOC. NEWALLOC provides more detailed information and will continue to execute even after errors are encountered. For information on the output provided by NEWALLOC, see the NEWALLOC option.

Note Before running DBCC CHECKALLOC or DBCC NEWALLOC on a database installed on read-only removable media, set the database status to 'read only' using sp_dboption. This will prevent the SHUTDOWN command from doing a checkpoint in the database. (Otherwise, checkpoint would attempt to write the allocation pages used by DBCC CHECKALLOC or DBCC NEWALLOC for scratch space and encounter I/O errors on the read-only media.

As an additional precaution, shut down and restart the server after DBCC has completed to prevent the possibility of a manual checkpoint attempting to write the allocation pages.

Also note that DBCC CHECKALLOC or DBCC NEWALLOC may print warning message 2558 for user objects residing on a removable device. You can ignore these messages. They arise because the segmap in sysusages for fragments corresponding to these devices is intentionally set to zero.

Important DBCC CHECKALLOC should be executed while minimal database activity is occurring. If DBCC CHECKALLOC is executed while transactions are in progress, the output may return spurious errors. To ensure that no other users have transactions in progress, set the database to readonly or single user with the sp_dboption system stored procedure.

CHECKCATALOG [(database_name)]
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.
CHECKTABLE (table_name [, NOINDEX | index_id])
Checks the specified table to see that index and data pages are correctly linked, that indexes are in proper 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.

In SQL Server 6.0, the performance of DBCC CHECKTABLE has been significantly improved by spawning multiple threads to automatically check nonclustered indexes in parallel. Because the table will be loaded in the data cache, each of the simultaneous threads will see an improvement because of an increase in the cache hit ratio.

If an index_id is specified, CHECKTABLE will check only that index.

CHECKDB [(database_name [, NOINDEX])]
Runs the same checks as CHECKTABLE, but on every table in the specified database. If database_name is not supplied, CHECKDB checks the current database.
CHECKIDENT [(table_name)]
Checks the current identity value and compares it with the maximum value in the identity column. If the current identity value is invalid, it will be reset using the maximum value in the identity column. Invalid identity information can cause SQL Server message 2627 when a PRIMARY KEY or UNIQUE KEY constraint exists on an identity column.
NOINDEX
Specifies that only the clustered index (the B-Tree and the data itself) is to be checked for user-defined tables. If no clustered index exists, only the data is checked. The NOINDEX option decreases the overall execution time of CHECKALLOC, CHECKDB, CHECKTABLE, and NEWALLOC because it does not check nonclustered indexes for user-defined tables. The NOINDEX option does not affect a check made against the system tables. When used against a system table(s), all clustered and nonclustered indexes are checked.
DBREPAIR (database_name, DROPDB [, NOINIT])
Drops the specified, and usually damaged, database. No user, including the person executing the statement, can be using the specified database when this DBCC statement is executed. When the NOINIT option is specified, the allocation pages of the dropped database are not modified. DBCC DBREPAIR is used for backward compatibility only. To drop a damaged database, use the DROP DATABASE statement. If the DROP DATABASE statement fails, use the sp_dbremove system stored procedure.
dllname (FREE)
Unloads the specified dynamic-link library (DLL) from SQL Server memory. After you execute an extended stored procedure, the DLL is loaded by SQL Server until the server is shut down. This statement also allows a DLL to be unloaded without shutting down SQL Server.
INPUTBUFFER (spid)
Returns one row containing the first 255 bytes of the "current input" buffer for the specified spid (system process ID taken from the sp_who system stored procedure output). This can be used to see the last query sent from the client. For processes that do not contain input streams, an error message is returned. DBCC INPUTBUFFER can be executed only by the system administrator.
MEMUSAGE
Provides detailed reports on memory use. DBCC MEMUSAGE reports three different types of information:
NEWALLOC [(database_name [, NOINDEX])]
Checks data and index pages against corresponding extent structures. DBCC NEWALLOC details all table information as well as provides the same summary information provided by DBCC CHECKALLOC, which is retained for compatibility. Unlike DBCC CHECKALLOC, DBCC NEWALLOC will not stop processing if it encounters an error.

DBCC NEWALLOC returns a listing for each allocation unit of the number of extents currently reserved for use by objects, the number of pages marked as being used by objects, and the actual number of pages being used by objects. An allocation unit can hold, at most, 32 database objects. An allocation unit is 512K (0.5 MB) made up of 32 extents (8 2K pages). From this, 512K/16K results in 32 objects. The used pages value and the ref pages value should be equivalent for most, if not all, allocation units. A difference of 7 is possible due to the transaction log. A slightly larger difference is acceptable when text or image data exists within the database.

The number of extents tells whether or not you can create new objects on that allocation unit. To estimate the total amount of space reserved by database objects (tables and indexes), multiply the total number of extents by 16K. To estimate the amount of that space being used by data or index information, multiply the total number of used pages by 2K.

Note Before running DBCC CHECKALLOC or DBCC NEWALLOC on a database installed on read-only removable media, set the database status to 'read only' using sp_dboption. This will prevent the SHUTDOWN command from doing a checkpoint in the database. (Otherwise, checkpoint would attempt to write the allocation pages used by DBCC CHECKALLOC or DBCC NEWALLOC for scratch space and encounter I/O errors on the read-only media.

As an additional precaution, shut down and restart the server after DBCC has completed to prevent the possibility of a manual checkpoint attempting to write the allocation pages.

Also note that DBCC CHECKALLOC or DBCC NEWALLOC may print warning message 2558 for user objects residing on a removable device. You can ignore these messages. They arise because the segmap in sysusages for fragments corresponding to these devices is intentionally set to zero.

Important DBCC NEWALLOC should be executed while minimal database activity is occurring. If DBCC NEWALLOC is executed while transactions are in progress, the output may return errors. To ensure that no other users have transactions in progress, set the database to read only or single user with the sp_dboption system stored procedure.

OPENTRAN ({database_name} | {database_id}) [WITH TABLERESULTS]
Displays information on the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. When the WITH TABLERESULTS option is not used, the results returned are formatted for easy readability. Use the WITH TABLERESULTS option for a table of results that can be inserted into a table for comparisons.

Note Results are displayed only if there is an active transaction or if the database contains replication information.

DBCC OPENTRAN is extremely useful in determining whether or not an open transaction exists within the log. When using the DUMP TRANSACTION statement, only the inactive portion of the log can be truncated, so an open transaction could cause the log not to be truncated completely. In earlier versions of SQL Server, all users needed to log off or the server needed to be shut down and restarted in order to clear the uncommitted transactions from the log. With DBCC OPENTRAN, an open transaction can be identified (the spid, the system process ID taken from the sp_who system stored procedure output, is returned) and terminated, if necessary.

OUTPUTBUFFER (spid)
Returns the "current output" buffer in hexadecimal and ASCII format for the specified spid. This can be used to see the results that were sent to the specified client (spid). For processes that do not contain output streams, an error message is returned. DBCC OUTPUTBUFFER can be executed only by the system administrator.
PERFMON
Provides a way to view all three types of SQLPERF statistics in order (IOSTATS, LRUSTATS, and NETSTATS). It takes no parameters.
PINTABLE (database_id, table_id)
Marks a table (data pages, text pages, and index pages) to remain in data cache (once used) until "unpinned." DBCC PINTABLE does not automatically read the table into cache; instead, as pages of the table are accessed and put into cache, they are marked so that they do not get flushed later. Once "pinned," data modifications are fully logged and the table can be recovered. Although this can provide performance improvements for some tables, it should be used with caution. As the table is read into cache, the size of the table is limited only by the available cache. If you are not careful, a large table could consume all the data cache, the server need to be restarted, and then the table unpinned. For more information, see DBCC UNPINTABLE, later in this section.
SHOW_STATISTICS (table_name, index_name)
Displays all the statistical information in the distribution page for an index (index_name) on a specified table (table_name). The results returned indicate the selectivity of an index (the lower the density returned, the higher the selectivity) and provide the basis for determining whether or not an index would be useful to the optimizer. The results returned are based on distribution steps of the index. To understand the output of SHOW_STATISTICS, see the examples, later in this section.

To see the last date the statistics were updated, use the system function STATS_DATE. For details, see the Functions topic.

SHOWCONTIG (table_id, [index_id])
Traverses the page chain at the leaf level of the specified index (index_id) or data level (if only table_id is specified) and determines whether the table is heavily fragmented. Table fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) made against the table. Because the normal distribution of these modifications are not usually equally distributed among the records of the table, the "fullness" of each page will begin to vary over time. For queries that scan part of or all of a table, this can cause additional page reads. When the table is heavily fragmented, you can reduce fragmentation and improve read-ahead (parallel data scan) performance by dropping and re-creating a clustered index (without using the SORTED_DATA option). Re-creating a clustered index will "compact" the data such that data pages are essentially full again; however, the level of "fullness" can also be configured with the FILLFACTOR option.

To find the index_id of a nonclustered index, specify the nonclustered index name (nc_index_name) in the following query:

SELECT indid FROM sysindexes
    WHERE name = 'nc_index_name'

DBCC SHOWCONTIG returns these statistics:

Statistic Description
Pages Scanned Number of pages in the table or index.
Extent Switches The number of times the DBCC statement left an extent while it was traversing the pages of the extent.
Avg. Pages per Extent The number of pages per extent in the page chain.
Scan Density [Best Count: Actual Count] Best Count is the ideal number of extent changes if everything is contiguously linked. Actual Count is the actual number of extent changes. The number in Scan Density is 100 if everything is contiguous; if it is below 100, some fragmentation exists. The Scan Density is a percentage.
Avg. Bytes free per page The average number of free bytes on the pages scanned. The higher the number, the less full the pages are; lower numbers are better. Be aware, however, that this number is also affected by row size, so a large row size may result in a higher number.
Avg. Page density (full) The average page density shows how full a page is (as a percentage). This value takes into account row size, so it is a more accurate indication of how full your pages are. The higher the percentage, the better.
Overflow Pages This value is for internal use.
Disconnected Overflow Pages This value is for internal use.

SHRINKDB (database_name [, new_size [, 'MASTEROVERRIDE']])
When specified with only the database_name parameter, DBCC SHRINKDB returns the minimum size to which this database can shrink, and it lists all objects and indexes that are preventing you from shrinking it further. You cannot shrink the database to a size smaller than the one returned; use caution and drop or move the database objects until the returned size is acceptable.

When new_size is specified, DBCC SHRINKDB shrinks the size of the specified database to the value, as specified in 2K pages by the new_size parameter. DBCC SHRINKDB may shrink both the data and log portions of the database. To change the size of only the data or the log, shrink the entire database first and then use the ALTER DATABASE statement to increase the size of the data or log portion of the database.

To shrink a user database, the database must be set to single user mode. Use the sp_dboption system stored procedure to set this database option. After setting the database to single-user mode, it is recommended that you dump both the master database and the database you are shrinking, prior to using DBCC SHRINKDB.

To shrink the master or tempdb databases you must start the server in single-user mode (use the sqlservr command-line executable with the -m parameter).

Important The database cannot be shrunk beyond either the size of the model database or to a size that is not a valid increment of allocation units. That is, new_size must be equal to or greater than the minimum size as returned by DBCC SHRINKDB plus any number of 512-byte increments (each of which is 256 2K pages). For example, if DBCC SHRINKDB returned a message that the 'Database can be shrunk to 5376 pages', the database can be shrunk to 5376 (10.5 MB), 5632 (11 MB), 5888 (11.5 MB), and so on. For more information, see the "Shrink a Database" example, later in this section.

After successfully shrinking a database to the desired size, back up both the master database and the database that has been shrunk.

DBCC SHRINKDB is fully logged and recoverable except when used on the master database. The MASTEROVERRIDE clause is required when decreasing the size of the master database. However, use caution if you need to shrink the size of the master database, because recovery could fail if the system fails while shrinking the master database. Back up the master database prior to shrinking it. You must be the system administrator or database owner to execute this statement.

SQLPERF ({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]}
{THREADS} {LOGSPACE})
Can provide information about performance statistics. Four performance options are available for this statement: IOSTATS, LRUSTATS, NETSTATS, and RASTATS. You can view replication statistics by using the sp_distcounters (subscription server statistics) and sp_replcounters (publication server statistics) system stored procedures.

IOSTATS generates the I/O statistics since the server was last started or since the statistics were last cleared.

LRUSTATS generates statistics about cache use since the server was last started or since the statistics were last cleared. (LRU stands for "least recently used" and is the algorithm used by the SQL Server cache manager.)

NETSTATS provides statistics about network use.

RASTATS provides statistics about Read Ahead.

The CLEAR option clears the statistics and does not display them.

The following list describes the statistics generated for each option:

IOSTATS
Statistic Definition
Batch Average Size The average number of pages written in a batch.
Batch Max. Size The maximum number of simultaneous outstanding physical I/Os.
Batch Writes The number of times multiple pages were written to disk in a batch.
Log Flush Average Log flush requests per physical write.
Log Flush Requests Number of requests to flush the log to disk.
Log Logical IO Average Logical pages written per physical write.
Log Logical Page IO Logical page I/O for transaction log.
Log Physical IO Physical I/O for transaction log.
Log Writes Total writes for the log.
Page Reads The number of 2K pages read from disk (physical reads).
Reads Outstanding The number of read requests issued to the operating system that have not been completed.
Single Page Writes The number of 2K pages written individually to disk (physical writes, including log writes).
Transactions The number of Transact-SQL batches executed. This value is useful in computing ratios involving log write numbers.
Transactions/Log Write The number of transaction records written to disk during each physical log write.
Writes Outstanding The number of write requests issued to the operating system that have not completed.

LRUSTATS
Statistic Definition
Cache Flushes The number of times a page needed to be flushed from cache to make room for another page.
Cache Hit Ratio The percentage of times a data page was found in the cache.
Cache Size The total number of pages in the cache.
Free Page Scan (Avg.) The average number of buffer pages that had to be scanned in order to find a free page.
Free Page Scan (Max.) The maximum number of buffer pages that had to be scanned in order to find a free page.
Free Buffers The number of buffers currently on the free list.
Min. Free Buffers The lazy writer will attempt to maintain at least this number of buffers on the free list.

DBCC SQLPERF (LRUSTATS) statistics are useful in determining whether you have a large enough cache in your system. After running SQL Server for a day or two, check the Cache Flushes and Average Free Page Scan. For optimal performance, Average Free Page Scan should be less than 10 and Cache Flushes should be less than 100. If you get higher numbers, it means that the SQL Server cache is not big enough to hold your working set. You can improve your server performance by increasing its cache size (adding more memory). If these numbers are below the recommended limits, it means that adding more memory to your system will not make a large difference in performance and that you have a cache big enough to cover the server's working set.

NETSTATS
Statistic Definition
Max. Worker Threads The highest number of worker threads that serviced the command queue since the network application was started or since the statistics were cleared.
Network Reads The total number of reads from the network.
Network Writes The total number of writes to the network.
Worker Threads The current number of worker threads servicing the command queue.

RASTATS
Statistic Definition
RA Pages Found in Cache How many pages the RA Manager found already in the cache when trying to perform scans.
RA Pages Placed in Cache How many pages were brought into the cache by the RA Manager.
RA Physical IO How many 16K reads were done by the RA Manager.
Used Slots How many RA slots are being used by active queries. Note that a single query may use multiple RA slots.

SQLPERF (THREADS)
Provides a mechanism to map the Windows NT system thread ID to a SQL Server spid. Because of thread pooling, many different Windows NT threads will service a single SQL Server spid over time, so this Windows NT system thread ID is usually not useful. This statement also returns the login name, the number of I/Os, and CPU and MEMUSAGE totals.
SQLPERF (LOGSPACE)
Returns the percentage of log space used at the time the statement is executed. This statement can be used only on a log that is on a separate device from its database.
TEXTALL [({database_name | database_id}[, FULL | FAST])]
Selects tables in the database that have text or image columns and runs TEXTALLOC on them. To select the speed at which the report is generated, choose FULL (all allocation pages in the database will be checked by the report) or FAST (does not generate an allocation report, but checks the linkage of the text chains and verifies that the pages in the chain are allocated). The default is FULL.
TEXTALLOC [({table_name | table_id}[, FULL | FAST])]
Checks the allocation of text or image columns for one table. To select the speed at which the report is generated, choose FULL (all allocation pages in the database will be looked at by the report) or FAST (does not generate an allocation report, but checks the linkage of the text chains and verifies that the pages in the chain are allocated). The default is FULL.
TRACEOFF (trace#)
Turns off the specified trace flag trace#. For a list of trace flags, see the Trace Flags topic.
TRACEON (trace#)
Turns on the specified trace flag trace#.
TRACESTATUS (trace# [, trace#...])
Displays the status for the specified trace flag(s). Can be 1 (on) or 0 (off). You can specify more than one trace flag by separating the numbers with a comma. If you specify - 1 for trace#, status information about all trace flags currently turned on will be returned.
UNPINTABLE (database_id, table_id)
Marks the table as no longer being RAM resident; however, the table will remain in cache until it is aged out of cache (and flushed to disk). "Pinning" a table is not recommended for widespread use; see DBCC PINTABLE, earlier in this section.
UPDATEUSAGE ({0 | database_name} [, table_name [, index_id]])
Reports and corrects inaccuracies in the sysindexes table that may result in incorrect space usage reports by the sp_spaceused system stored procedure. This command corrects the rows, used, reserved, and dpages columns of the sysindexes table for any clustered indexes on objects of the type U (user-defined table) or S (system table). Size information is not maintained for nonclustered indexes. It can be used to synchronize space usage counters in sysindexes, which will result in accurate usage information being returned. When 0 is used instead of the database_name, the update is performed in the current database.

When processing, UPDATEUSAGE acquires a shared table lock on the table being processed, so updates to the sysindexes row for syslogs can be performed only if the database is in single user mode and when the syslogs table is specified. This prevents any logging during the update and ensures accurate changes. All other changes to sysindexes are fully logged.

If there are no inaccuracies in sysindexes, DBCC UPDATEUSAGE will return no data. Otherwise, data will be returned that shows you what rows and columns are being updated in sysindexes.

Note This command may take some time to run if you run it on large tables or databases, so it should typically be used only when you suspect incorrect values returned by the sp_spaceused system stored procedure or via a timed operation to run during off hours.

USEROPTIONS
Returns the list of SET options that are active (or set) for the current connection. It takes no parameters. For details and a listing of the session options available, see the SET statement.
WITH NO_INFOMSGS
Prevent the printing of informational messages (severity 1 through 10). This helps to determine real problems when using verbose commands such as DBCC CHECKDB.

Permission

Permission to execute the DBCC statement varies for each of the DBCC options. Permission is not transferable. DBCC permissions are:

DBCC option Permission defaults to
CHECKALLOC SA or DBO
CHECKCATALOG SA or DBO
CHECKDB SA or DBO
CHECKTABLE SA, DBO, or the table owner
DBREPAIR SA only
dllname SA only
INPUTBUFFER SA only
MEMUSAGE Any user
NEWALLOC SA or DBO
OPENTRAN SA or DBO
OUTPUTBUFFER SA only
PERFMON SA only
PINTABLE SA only
SHOW_STATISTICS SA, DBO, or the table owner
SHOWCONTIG SA or DBO
SHRINKDB SA or DBO
SQLPERF Any user
TEXTALL SA or DBO
TEXTALLOC SA or DBO
TRACEOFF SA only
TRACEON SA only
TRACESTATUS SA only
UNPINTABLE SA only
UPDATEUSAGE SA or DBO
USEROPTIONS Any user

Examples

A.    Check the Allocation Units (CHECKALLOC)

This example runs the DBCC CHECKALLOC command against the pubs database.

DBCC CHECKALLOC(pubs)
go
Checking pubs
Alloc page 0 (# of extent=32 used pages=58 ref pages=58)
Alloc page 256 (# of extent=26 used pages=37 ref pages=37)
Alloc page 512 (# of extent=14 used pages=40 ref pages=40)
Alloc page 768 (# of extent=1 used pages=8 ref pages=2)
Alloc page 1024 (# of extent=1 used pages=0 ref pages=0)
Alloc page 1280 (# of extent=1 used pages=0 ref pages=0)
Total (# of extent=75 used pages=143 ref pages=137) in this database
DBCC execution completed. If DBCC printed error messages, see your System Administrator.

This database contains six allocation units of 256 2K pages (0.5 MB) each, giving 1536 2K pages in all (3 MB). Of that 3 MB, 88 extents (88 * 16K = 1408K), is reserved and 215 pages (or 215 * 2K = 430K) are used by data and indexes. The reserved 88 extents include objects that have been created but not yet completely populated with data. Used pages refer to those pages that do contain actual data or index pages.

B.    Check a Single Table (CHECKTABLE)

This example runs the DBCC CHECKTABLE command against all indexes and data on the employee table in the pubs database.

USE pubs
go
DBCC CHECKTABLE(employee)
go
Checking employee
The total number of data pages in this table is 2.
Table has 43 data rows.
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
C.    Check a Table with NOINDEX (CHECKTABLE)

This example skips all of the nonclustered indexes.

USE pubs
go
DBCC CHECKTABLE(employee, NOINDEX)
go
Checking employee
WARNING: NOINDEX option of 'CHECKTABLE' being used, checks on non-system indexes will be skipped
The total number of data pages in this table is 2.
Table has 43 data rows.
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
D.    Check the Transaction Log (CHECKTABLE)

This example runs the DBCC CHECKTABLE command against the syslogs table within the sales database.

USE sales
go
DBCC CHECKTABLE(syslogs)
go
Checking syslogs
The total number of data pages in this table is 145.
*** NOTICE:  Space used on the log segment is 0.29 Mbytes, 4.72.
*** NOTICE:  Space free on the log segment is 5.85 Mbytes, 95.28.
Table has 3194 data rows.
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
E.    Report Memory Usage (MEMUSAGE)

The report given by DBCC MEMUSAGE is broken down into three parts. Only 3 of the 20 procedures are shown here.

DBCC MEMUSAGE
go
Memory Usage:
 
 
                    Meg.         2K Blks           Bytes
 
      Configured Memory:  8.0000        4096     8388608
              Code size:  1.7166         879     1800000
      Static Structures:  0.2385         123      250064
                  Locks:  0.2480         127      260000
           Open Objects:  0.1068          55      112000
         Open Databases:  0.0031           2        3220
     User Context Areas:  0.8246         423      864688
             Page Cache:  3.3040        1692     3464544
           Proc Headers:  0.0796          41       83448
        Proc Cache Bufs:  1.3379         685     1402880
 
Buffer Cache, Top 20:
 
       DB Id     Object Id    Index Id    2K Buffers
 
           4             5           0          26
           1            56           0          20
           1                 1       0          16
           1     576005083           0          11
           4             3           0          10
           4            99           0           6
           1             2           0           5
           1             1           2          17
           1             5           2           4
           1           399           0           4
           4             1           0           4
           4             2           0           4
           1             3           0           3
           4             1           0           3
           4             6           0           3
           4     192003715           0           3
           1             5           0           2
           1            45           1           2
           2             2         255           2
           2            99           0           2
 
 
 
Procedure Cache, Top 5:
 
 
Procedure Name: sp_helpconstraint sp_server_info
Database Id: 1
Object Id: 1516532436
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 1
Size of plans: 0.002974 Mb, 3118.000000 bytes, 42 pages
 
 
Procedure Name: CA only
Database Id: 4
Object Id: 1104006964
Version: 0
Uid: 1
Type: stored view
Number of trees: 1
Size of trees: 0.001659 Mb, 1740.000 bytes, 1 pages
Number of plans: 0
Size of plans: 0.000000 Mb, 00000.000000 bytes, 0 pages
 
 
Procedure Name: categories
Database Id: 4
Object Id: 1168007192
Version: 0
Uid: 1
Type: stored view
Number of trees: 1
Size of trees: 0.001883 Mb, 1974.00000 bytes, 1 pages
Number of plans: 0
Size of plans: 0.000000 Mb, 0.000000 bytes, 0 pages
.
.
.
F.    View Index Statistics (SHOW_STATISTICS)

This table is a copy of the authors table created in the pubs database. The authors table includes a clustered index on the primary key (au_id) column and a nonclustered index on the composite key (au_lname, au_fname). This example shows the DBCC output for each index after 1500 rows are inserted and the statistics have been updated.

The first example shows statistics for the clustered primary key index on the au_id column. Because a PRIMARY KEY constraint enforces uniqueness, the selectivity of this index is highly selective (only 1 row will match). In this case, the Density * Rows is 1.0, showing that there is only 1 match. If a query were executed against the authors table using a WHERE clause specifying equality with an au_id, this index would be used.

DBCC SHOW_STATISTICS (authors, UPKCL_auidind)
go

 Updated                  Rows    Steps       Density              
 -------------------- -------- -------- ------------- 
 Apr 17 1995  1:22PM      1520      109      0.000658 

(1 row affected)

 All density          Columns                        
 -------------------- ------------------------------ 
             0.000658 au_id                          

(1 row affected)

 Steps       
 ----------- 
 100-10-1000 
.
.
.
 724-08-9931 

(109 rows affected)

DBCC execution completed. If DBCC printed error messages, see your
System Administrator.

The second example shows statistics for the nonclustered index on the au_lname and au_fname columns. Because this index is not unique, the selectivity is based on the distribution of the data and the likelihood of duplicates. In this case, the Density * Rows is 20.8, showing that there are roughly 21 duplicates based on the au_lname, column alone, not counting any values in more than one step (values with an extremely high number of duplicates and low selectivity).

The All Density is more accurate and shows the selectivity of each column, including those that are more than two steps (lowering the selectivity further and emphasizing how a large number of duplicates can affect the effectiveness of an index). Looking at the au_lname column only, the selectivity is not very high (48 potential duplicates) and an index might be used; however, the selectivity of au_lname, au_fname is much higher (only 3 potential duplicates). If both the au_lname and au_fname are specified with a WHERE clause, it is likely that this index will be used.

DBCC SHOW_STATISTICS (authors, aunmind)
go

 Updated                  Rows    Steps       Density              
 -------------------- -------- -------- ------------- 
 Apr 17 1995  1:22PM      1520       39      0.013674 

(1 row affected)

 All density          Columns                        
 -------------------- ------------------------------ 
             0.031665 au_lname                       
             0.002028 au_lname, au_fname             

(2 rows affected)

 Steps                                    
 ---------------------------------------- 
 Abbott                                   
.
.
.
 Xenon                                    

(39 rows affected)

DBCC execution completed. If DBCC printed error messages, see your
System Administrator.
The inserts are performed and DBCC is executed again.
G.    Shrink a Database (SHRINKDB)

This example defines the steps to ensure a proper decrease in size of a database:

  1. Set the database to single-user mode using the sp_dboption system stored procedure.
  2. Change your current database to the database you want to shrink.
  3. Verify how small you can make the database. (Run DBCC SHRINKDB without specifying a new_size.)
  4. Shrink the database.
USE master
go
sp_dboption 'sales', 'single user', true
go
USE sales
go
DBCC SHRINKDB(sales)
go
Current size of database Size database can be shrunk to 
------------------------ ------------------------------ 
1536                     1024                           

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, see your System Administrator.

DBCC SHRINKDB reported a minimum size of 5376 pages. Because the absolute minimum is not always necessary, shrink the sales database down to a new size of 12 MB.

DBCC SHRINKDB(sales, 6144)
go
DBCC SHRINKDB running on database "sales"...
DBCC execution completed. If DBCC printed error messages, see your System Administrator.

See Also

DROP DATABASE sp_configure
RECONFIGURE sp_helpdb