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.
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
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.
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.
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.
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.
To see the last date the statistics were updated, use the system function STATS_DATE. For details, see the Functions topic.
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. |
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.
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. |
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.
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 |
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.
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.
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.
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.
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
.
.
.
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.
This example defines the steps to ensure a proper decrease in size of a 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.
| DROP DATABASE | sp_configure |
| RECONFIGURE | sp_helpdb |