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 storingvarbinary(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
No comments:
Post a Comment