DBCC Commands for SQL DBA

This is a work-in-progress document and will be updated whenever I have more to add to it :)
A good set of DBCC commands that a DBA should use regularly...

1: DBCC CHECKDB
- Checks the allocation, structural, and logical integrity of all the objects in the specified database.

-- Check the current database.
DBCC CHECKDB
GO

-- Check the database without nonclustered indexes.
DBCC CHECKDB ('DatabaseName', NOINDEX);
GO

2: DBCC CHECKTABLE
-- Checks the integrity of all the pages and structures that make up the table or indexed view
-- DBCC CHECKTABLE performs a physical consistency check on a single table or indexed view and
-- all its nonclustered and XML indexes, unless the NOINDEX option is specified.
-- For the specified table, DBCC CHECKTABLE checks for the following:
-- Index, in-row, LOB, and row-overflow data pages are correctly linked.
-- Indexes are in their correct sort order.
-- Pointers are consistent.
-- The data on each page is reasonable, included computed columns.
-- Page offsets are reasonable.
-- Every row in the base table has a matching row in each nonclustered index, and vice-versa.
-- Every row in a partitioned table or index is in the correct partition.
-- DBCC CHECKTABLE uses an internal database snapshot to provide the transactional consistency that it must have to perform these checks.

-- If DBCC CHECKTABLE reports any errors, we recommend restoring the database from the database backup instead of running REPAIR with
-- one of the REPAIR options. If no backup exists, running REPAIR can correct the errors that are reported.
-- The REPAIR option to use is specified at the end of the list of reported errors.
-- However, that correcting the errors by using the REPAIR_ALLOW_DATA_LOSS option might require that some pages, and therefore data, be deleted.

-- The repair can be performed under a user transaction to allow the user to roll back the changes that have been made.
-- If repairs are rolled back, the database will still contain errors and must be restored from a backup. After you have completed all repairs, back up the database.

-- To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

USE DatabaseName
GO
DBCC CHECKTABLE ('Owner.TableName')
GO

-- The following example performs a low overhead check of the Employee table in the AdventureWorks database.
USE DatabaseName
GO
DBCC CHECKTABLE ('Owner.TableName') WITH PHYSICAL_ONLY;
GO


3: DBCC DBREINDEX
-- DBCC DBREINDEX enables DBAs to rebuild indexes without having to drop and recreate PRIMARY KEY and UNIQUE constraints
–- Locks the table for the duration of the operation
–- Can offer additional optimizations than a series of individual DROP INDEX and CREATE INDEX statements on a single table

USE DatabaseName
GO
DBCC DBREINDEX ('Owner.TableName', IndexName, FillFactorNumber)
GO

3: DBCC CHECKCATALOG
-- Checks for catalog consistency within the specified database. The database must be online.
-- DBCC CHECKCATALOG will check that every table and view in sysobjects has at least one column
-- in syscolumns and that every data type in syscolumns has a matching entry in systypes.

-- Check the current database.
DBCC CHECKCATALOG
GO
-- Check a particular database.
DBCC CHECKCATALOG ('DatabaseName');
GO

4: DBCC INPUTBUFFER
-- Displays the last statement sent from a client to an instance of SQL Server

DBCC INPUTBUFFER (SPID)

5: DBCC CHECKALLOC
-- Checks the consistency of disk space allocation structures for a specified database.
-- DBCC CHECKALLOC checks the allocation of all pages in the database, regardless of the type of page or type of object to
-- which they belong. It also validates the various internal structures that are used to keep track of these pages
-- and the relationships between them.

-- Check the current database.
DBCC CHECKALLOC
GO
-- Check a particular database.
DBCC CHECKALLOC ('DatabaseName')
GO

-- Other Options available with DBCC CHECKALLOC:
a: REPAIR_ALLOW_DATA_LOSS
Tries to repair any errors that are found. These repairs can cause some data loss. REPAIR_ALLOW_DATA_LOSS is the only option that allows for allocation errors to be repaired

b: REPAIR_FAST
Maintained for backward compatibility only.

Use the REPAIR options only as a last resort. Repair operations do not consider any of the constraints that may exist on or between tables.
If the specified table is involved in one or more constraints, run DBCC CHECKCONSTRAINTS after a repair operation.
If you must use REPAIR, run DBCC CHECKDB to find the repair level to use.
If you are going to use the REPAIR_ALLOW_DATA_LOSS level, back up the database before you run DBCC CHECKDB.

6: DBCC SHOWCONTIG
-- This command shows the degree of contiguous values in a clustered index. If the metrics are poor, then you can drop and recreate the clustered index.
-- You may use either table name and index name, or table ID and index ID numbers.

USE DatabaseName
GO
DBCC SHOWCONTIG (TableName, IndexName)
GO

Results look like this:
/* --- -- */
DBCC SHOWCONTIG scanning 'client' table...
Table: 'client' (767927149); index ID: 1, database ID: 33
TABLE level scan performed.
- Pages Scanned................................: 3333
- Extents Scanned..............................: 436
- Extent Switches..............................: 498
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 83.57% [417:499]
- Logical Scan Fragmentation ..................: 1.29%
- Extent Scan Fragmentation ...................: 40.37%
- Avg. Bytes Free per Page.....................: 681.2
- Avg. Page Density (full).....................: 91.58%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
/* --- -- */

7: DBCC SHRINKDATABASE
-- Shrinks the size of the data files in the specified database.
-- To shrink all data and log files for a specific database, execute DBCC SHRINKDATABASE.
-- To shrink one data or log file at a time for a specific database, execute DBCC SHRINKFILE.
-- DBCC SHRINKDATABASE and DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained.

DBCC SHRINKDATABASE (DatabaseName, FreeSpacePercentNumber)
GO


8: DBCC SHRINKFILE
-- Shrinks the size of the specified data file or log file for the related database.
-- The database cannot be made smaller than the size of the model database.
-- Use DBCC SHRINKFILE to shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.
-- To remove any data that may be in a file, execute DBCC SHRINKFILE('file_name', EMPTYFILE) before executing ALTER DATABASE.

USE DatabaseName
GO
DBCC SHRINKFILE (DatabaseName, NewFileSizeInMB)
GO

9: DBCC SHOW_STATISTICS
-- Displays the current distribution statistics for the specified target on the specified table.
-- 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 an index is useful to the query optimizer.
-- The results returned are based on distribution steps of the index.

-- Result set explained:
-- Updated: The date and time the index statistics were last updated
-- Rows: The total number of rows in the table
-- Rows Sampled: The number of rows sampled for index statistics information
-- Steps: The number of distribution steps
-- Density: The selectivity of the first index column prefix
-- Average key length: The average length of the first index column prefix
-- All density: The selectivity of a set of index column prefixes
-- Average length: The average length of a set of index column prefixes
-- Columns: The names of index column prefixes for which All density and Average length are displayed
-- RANGE_HI_KEY: The upper bound value of a histogram step
-- RANGE_ROWS: The number of rows from the sample that fall within a histogram step, not counting the upper bound
-- EQ_ROWS: The number of rows from the sample that are equal in value to the upper bound of the histogram step
-- DISTINCT_RANGE_ROWS: The number of distinct values within a histogram step, not counting the upper bound
-- AVG_RANGE_ROWS: The average number of duplicate values within a histogram step, not counting the upper bound (where RANGE_ROWS / DISTINCT_RANGE_ROWS for DISTINCT_RANGE_ROWS > 0)

USE DatabaseName
GO
DBCC SHOW_STATISTICS ('Owner.TableName', IndexName)
GO

10: DBCC SQLPERF
-- Provides statistics about how the transaction-log space was used in all databases.

DBCC SQLPERF(LOGSPACE)
GO

11: DBCC PROCCACHE
-- Displays information in a table format about the procedure cache.
-- The procedure cache is used to cache the compiled and executable plans to speed up the execution of batches.
-- The entries in a procedure cache are at a batch level. The procedure cache includes the following entries:
-- Compiled plans
-- Execution plans
-- Algebrizer tree
-- Extended procedures

-- Result Set Explained:
-- num proc buffs = Total number of pages used by all entries in the procedure cache.

-- num proc buffs used = Total number of pages used by all entries that are currently being used.

-- num proc buffs active = For backward compatibility only. Total number of pages used by all entries that are currently being used.

-- proc cache size = Total number of entries in the procedure cache.

-- proc cache used = Total number of entries that are currently being used.

-- proc cache active = For backward compatibility only. Total number of entries that are currently being used.

DBCC PROCCACHE
GO