1.
DBCC CHECKDB
Checks
the logical and physical integrity of all the objects in the specified database
by performing the following operations:
Runs DBCC CHECKALLOC on the database.
Runs DBCC CHECKTABLE on every table and view in the database.
Runs DBCC CHECKCATALOG on the database.
Validates the contents of every indexed view in the database.
Validates
link-level consistency between table metadata and file system directories and
files when storing varbinary(max) data in the file system using
FILESTREAM.
Syntax:
DBCC
CHECKDB
[
[
( database_name | database_id | 0
[
, NOINDEX
|
, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
)
]
[
WITH
{
[
ALL_ERRORMSGS ]
[
, EXTENDED_LOGICAL_CHECKS ]
[
, NO_INFOMSGS ]
[
, TABLOCK ]
[
, ESTIMATEONLY ]
[
, { PHYSICAL_ONLY | DATA_PURITY } ]
}
]
]
Example:
--
Check the current database.
DBCC
CHECKDB;
GO
--
Check the AdventureWorks database without nonclustered indexes.
DBCC
CHECKDB (AdventureWorks, NOINDEX);
GO
2.
DBCC CHECKTABLE
Check
the integrity of all the pages and structures that make up the table or indexed
view.
Syntax:
DBCC
CHECKTABLE
(
table_name
|
view_name
[
, { NOINDEX | index_id }
|,
{ REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }
]
)
[
WITH
{
ALL_ERRORMSGS ]
[
, EXTENDED_LOGICAL_CHECKS ]
[
, NO_INFOMSGS ]
[
, TABLOCK ]
[ , ESTIMATEONLY ]
[
, { PHYSICAL_ONLY | DATA_PURITY } ]
}
]
Example:
USE
AdventureWorks;
GO
DBCC
CHECKTABLE ("HumanResources.Employee");
GO
3.
DBCC CHECKALLOC
Check
the consistency of disk space allocation structures for a specified database.
Syntax:
DBCC
CHECKALLOC
[
(
database name | database_id | 0
[
, NOINDEX
|
, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
)
[
WITH
{
[
ALL_ERRORMSGS ]
[
, NO_INFOMSGS ]
[
, TABLOCK ]
[
, ESTIMATEONLY ]
}
]
]
Example:
--
Check the current database.
DBCC
CHECKALLOC;
GO
--
Check the AdventureWorks database.
DBCC
CHECKALLOC (AdventureWorks);
GO
4.
DBCC SHOWCONTIG
Display
fragmentation information for the data and indexes of the specified table or
view.
Syntax:
DBCC
SHOWCONTIG
[
(
{
table_name | table_id | view_name | view_id }
[
, index_name | index_id ]
)
]
[
WITH
{
[
, [ ALL_INDEXES ] ]
[
, [ TABLERESULTS ] ]
[
, [ FAST ] ]
[
, [ ALL_LEVELS ] ]
[
NO_INFOMSGS ]
}
]
Example:
USE
AdventureWorks;
GO
DBCC
SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
GO
5.
DBCC DBREINDEX
Rebuild
one or more indexes for a table in the specified database.
Syntax:
DBCC
DBREINDEX
(
table_name
[
, index_name [ , fillfactor ] ]
)
[
WITH NO_INFOMSGS ]
Example:
USE
AdventureWorks;
GO
DBCC
DBREINDEX ("HumanResources.Employee", " ", 70);
GO
6.
DBCC INDEXDEFRAG
Defragment
indexes of the specified table or view.
Syntax:
DBCC
INDEXDEFRAG
(
{
database_name | database_id | 0 }
,
{ table_name | table_id | view_name | view_id }
[
, { index_name | index_id } [ , { partition_number | 0 } ] ]
)
[
WITH NO_INFOMSGS ]
Example:
DBCC
INDEXDEFRAG (AdventureWorks, "Production.Product",
PK_Product_ProductID)
GO
7.
DBCC SHOW_STATISTICS
DBCC
SHOW_STATISTICS displays current query optimization statistics for a table or
indexed view. The query optimizer uses statistics to estimate the cardinality
or number of rows in the query result, which enables the query optimizer to
create a high quality query plan.
Syntax:
DBCC
SHOW_STATISTICS ( table_or_indexed_view_name , target )
[
WITH [ NO_INFOMSGS ] < option > [ , n ] ]
Example:
USE
AdventureWorks;
GO
DBCC
SHOW_STATISTICS ("Person.Address", AK_Address_rowguid) WITH
HISTOGRAM;
GO
8.
DBCC DROPCLEANBUFFERS
Remove
all clean buffers from the buffer pool.
Syntax:
DBCC
DROPCLEANBUFFERS [ WITH NO_INFOMSGS ]
Example:
DBCC
DROPCLEANBUFFERS WITH NO_INFOMSGS
9.
DBCC OPENTRAN
Display
information about the oldest active transaction and the oldest distributed and
nondistributed replicated transactions, if any, within the specified database.
Syntax:
DBCC
OPENTRAN
[
(
[ database_name | database_id | 0 ] ) ]
{
[ WITH TABLERESULTS ]
[
, [ NO_INFOMSGS ] ]
}
]
Example:
DBCC
OPENTRAN
10.
DBCC SQLPERF()
Provide
transaction log space usage statistics for all databases. It can also be used
to reset wait and latch statistics.
Syntax:
DBCC
SQLPERF
(
[
LOGSPACE ]
|
[
"sys.dm_os_latch_stats" , CLEAR ]
|
[
"sys.dm_os_wait_stats" , CLEAR ]
)
[WITH
NO_INFOMSGS ]
Example:
DBCC
SQLPERF(LOGSPACE)
11.
DBCC TRACEON
Enable
the specified trace flags.
Syntax:
DBCC
TRACEON ( trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]
Example:
DBCC
TRACEON (3205);
GO
12.
DBCC TRACEOFF
Disable
the specified trace flags.
Syntax:
DBCC
TRACEOFF ( trace# [ ,...n ] [ , -1 ] ) [ WITH NO_INFOMSGS ]
Example:
DBCC
TRACEOFF (3205);
GO
13.
DBCC UPDATEUSAGE
Reports
and corrects pages and row count inaccuracies in the catalog views. These
inaccuracies may cause incorrect space usage reports returned by the sp_spaceused
system stored procedure.
Syntax:
DBCC
UPDATEUSAGE
(
{ database_name | database_id | 0 }
[
, { table_name | table_id | view_name | view_id }
[
, { index_name | index_id } ] ]
)
[ WITH [ NO_INFOMSGS ] [ , ] [ COUNT_ROWS ] ]
Example:
DBCC
UPDATEUSAGE (0);
GO
14.
DBCC TRACESTATUS
Display
the status of trace flags.
Syntax:
DBCC
TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] )
[
WITH NO_INFOMSGS ]
Example:
DBCC
TRACESTATUS (2528, 3205);
GO
15.
DBCC SHRINKDATABASE
Shrink
the size of the data and log files in the specified database.
Syntax:
DBCC
SHRINKDATABASE
(
database_name | database_id | 0
[
, target_percent ]
[
, { NOTRUNCATE | TRUNCATEONLY } ]
)
[
WITH NO_INFOMSGS ]
Example:
DBCC
SHRINKDATABASE (AdventureWorks, TRUNCATEONLY);
16.
DBCC SHRINKFILE
Shrinks
the size of the specified data or log file for the current database or empties
a file by moving the data from the specified file to other files in the same
filegroup, allowing the file to be removed from the database.
Syntax:
DBCC
SHRINKFILE
(
{
file_name | file_id }
{
[ , EMPTYFILE ]
|
[ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[
WITH NO_INFOMSGS ]
Example:
USE
UserDB;
GO
DBCC
SHRINKFILE (DataFile1, 7);
GO
17.
DBCC CHECKIDENT
Checks
the current identity value for the specified table and, if it is needed,
changes the identity value. You can also use DBCC CHECKIDENT to manually set a
new current identity value for the identity column.
Syntax:
DBCC
CHECKIDENT
(
table_name
[
, { NORESEED | { RESEED [ , new_reseed_value ] } } ]
)
[
WITH NO_INFOMSGS ]
Example:
USE
AdventureWorks;
GO
DBCC
CHECKIDENT ("HumanResources.Employee");
GO
18.
DBCC CLEANTABLE
Reclaims
space from dropped variable-length columns in tables or indexed views.
Syntax:
DBCC
CLEANTABLE
(
{
database_name | database_id | 0 }
,
{ table_name | table_id | view_name | view_id }
[
, batch_size ]
)
[
WITH NO_INFOMSGS ]
Example:
DBCC
CLEANTABLE (AdventureWorks,"Production.Document", 0)
WITH
NO_INFOMSGS;
GO
19.
DBCC USEROPTIONS
Returns
the SET options active (set) for the current connection.
Syntax:
DBCC
USEROPTIONS
[
WITH NO_INFOMSGS ]
Example:
DBCC
USEROPTIONS;
20.
DBCC CHECKCONSTRAINTS
Check
the integrity of a specified constraint or all constraints on a specified table
in the current database.
Syntax:
DBCC
CHECKCONSTRAINTS
[
(
table_name
| table_id | constraint_name | constraint_id
)
]
[
WITH
[
{ ALL_CONSTRAINTS | ALL_ERRORMSGS } ]
[
, ] [ NO_INFOMSGS ]
]
Example:
USE
AdventureWorks;
GO
DBCC
CHECKCONSTRAINTS ("Production.CK_ProductCostHistory_EndDate");
GO
21.
DBCC CHECKCATALOG
Check
for catalog consistency within the specified database. The database must be
online.
Syntax:
DBCC
CHECKCATALOG
[
(
database_name
| database_id | 0
)
]
[
WITH NO_INFOMSGS ]
Example:
DBCC
CHECKCATALOG (AdventureWorks);
GO
22.
DBCC CHECKFILEGROUP
Check
the allocation and structural integrity of all tables and indexed views in the
specified filegroup of the current database.
Syntax:
DBCC
CHECKFILEGROUP
[
[
( { filegroup_name | filegroup_id | 0 }
[
, NOINDEX ]
)
]
[
WITH
{
[
ALL_ERRORMSGS | NO_INFOMSGS ]
[
, TABLOCK ]
[
, ESTIMATEONLY ]
[
, PHYSICAL_ONLY ]
}
]
]
Example:
USE
AdventureWorks;
GO
DBCC
CHECKFILEGROUP;
GO
