DBCC Commands

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server. DBCC commands take input parameters and return values.

Database Console Command statements are grouped into the following categories.

CategoryDescription
Maintenance Maintenance tasks on a database, index, or filegroup.
Miscellaneous Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
Informational Tasks that gather and display various types of information.
Validation Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

Maintenance Statements

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 (AdventureWorks2012,'Production.Document', 0)  
WITH NO_INFOMSGS;  
GO 

DBCC DBREINDEX

Rebuilds one or more indexes for a table in the specified database.

Syntax:
DBCC DBREINDEX   
(   
    table_name   
    [ , index_name [ , fillfactor ] ]  
)  
    [ WITH NO_INFOMSGS ]
Example:
USE AdventureWorks2012;   
GO  
DBCC DBREINDEX ('HumanResources.Employee', PK_Employee_BusinessEntityID,80);  
GO

DBCC DROPCLEANBUFFERS

Removes all clean buffers from the buffer pool, and columnstore objects from the columnstore object pool.

Syntax:
DBCC DROPCLEANBUFFERS [ WITH NO_INFOMSGS ] 
Example:
DBCC DROPCLEANBUFFERS ( COMPUTE | ALL ) [ WITH NO_INFOMSGS ]  

DBCC FREEPROCCACHE

Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

Syntax:
DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]
Example:
USE AdventureWorks2012;  
GO  
SELECT * FROM Person.Address;  
GO  
SELECT plan_handle, st.text  
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st  
WHERE text LIKE N'SELECT * FROM Person.Address%';  
GO  

DBCC INDEXDEFRAG

Defragments 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 (AdventureWorks2012, 'Production.Product', PK_Product_ProductID);  
GO 

DBCC SHRINKDATABASE

Shrinks 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:
Shrinking a database and specifying a percentage of free space
DBCC SHRINKDATABASE (UserDB, 10);  
GO 

Truncating a database

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY);

DBCC SHRINKFILE

Shrinks the current database’s specified data or log file size. You can use it to move data from one file to other files in the same filegroup, which empties the file and allows for its database removal. You can shrink a file to less than its size at creation, resetting the minimum file size to the new value.

Syntax:
DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]  
    }  
)  
[ WITH NO_INFOMSGS ] 
Example:
--Shrinking a data file to a specified target size
USE UserDB;  
GO  
DBCC SHRINKFILE (DataFile1, 7);  
GO
Shrinking a log file to a specified target size
USE AdventureWorks2012;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE AdventureWorks2012  SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  DBCC SHRINKFILE (AdventureWorks2012_Log, 1);  
GO  
-- Reset the database recovery model.  
ALTER DATABASE AdventureWorks2012  SET RECOVERY FULL;  
GO 

Truncating a data file:
USE AdventureWorks2012;  
GO  
SELECT file_id, name  FROM sys.database_files;  
GO  
DBCC SHRINKFILE (1, TRUNCATEONLY); 

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:
Updating page or row counts or both for all objects in the current database
DBCC UPDATEUSAGE (0);  
GO  
Updating page or row counts or both for AdventureWorks, and suppressing informational messages
DBCC UPDATEUSAGE (AdventureWorks2012) WITH NO_INFOMSGS;   
GO 
Updating page or row counts or both for the Employee table
DBCC UPDATEUSAGE (AdventureWorks2012,'HumanResources.Employee');  
GO 
Updating page or row counts or both for a specific index in a table
DBCC UPDATEUSAGE (AdventureWorks2012, 'HumanResources.Employee', IX_Employee_OrganizationLevel_OrganizationNode);  
GO 

Miscellaneous Statements

DBCC dllname (FREE)

Unloads the specified extended stored procedure DLL from memory.

Syntax:
DBCC <dllname> ( FREE ) [ WITH NO_INFOMSGS ] 
Example:
DBCC xp_sample (FREE);

DBCC FLUSHAUTHCACHE

Empties the database authentication cache containing information about logins and firewall rules, for the current user database in SQL Database. This statement doesn’t apply to the logical master database, because the master database contains the physical storage for the information about logins and firewall rules. The user executing the statement and other currently connected users remain connected.

Syntax:
DBCC FLUSHAUTHCACHE [ ; ]
Example:
DBCC FLUSHAUTHCACHE;

DBCC HELP

DBCC HELP returns a result set displaying the syntax for the specified DBCC command.

Syntax:
DBCC HELP ( 'dbcc_statement' | @dbcc_statement_var | '?' )  
[ WITH NO_INFOMSGS ] 
Example:
Using DBCC HELP with a variable
DECLARE @dbcc_stmt sysname;  
SET @dbcc_stmt = 'CHECKDB';  
DBCC HELP (@dbcc_stmt);  
GO 

Using DBCC HELP with the ? option
DBCC HELP ('?');  
GO 

DBCC TRACEOFF

Disables the specified trace flags.

Syntax:
DBCC TRACEOFF ( trace# [ ,...n ] [ , -1 ] ) [ WITH NO_INFOMSGS ]
Example:
DBCC TRACEOFF (3205);   
GO

DBCC FREESESSIONCACHE

Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

Syntax:
DBCC FREESESSIONCACHE [ WITH NO_INFOMSGS ] 
Example:
USE AdventureWorks2012;  
GO  
DBCC FREESESSIONCACHE WITH NO_INFOMSGS;  
GO 

DBCC TRACEON

Enables the specified trace flags.

Syntax:
DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ] 

trace#
Is the number of the trace flag to turn on.
n
Is a placeholder that indicates multiple trace flags can be specified.
-1
Switches on the specified trace flags globally. This argument is required in Azure SQL Managed Instance.
WITH NO_INFOMSGS
Suppresses all informational messages.
Example:
The following example disables hardware compression for tape drivers, by switching on trace flag 3205. This flag is switched on only for the current connection.
DBCC TRACEON (3205);  
GO 

The following example switches on trace flag 3205 globally.
DBCC TRACEON (3205, -1);  
GO  

The following example switches on trace flags 3205, and 260 globally.
DBCC TRACEON (3205, 260, -1);  
GO 

DBCC FREESYSTEMCACHE

Releases all unused cache entries from all caches. The SQL Server Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries. However, you can use this command to manually remove unused entries from every cache or from a specified Resource Governor pool cache.

Syntax:
DBCC FREESYSTEMCACHE   
    ( 'ALL' [, pool_name ] )   
    [WITH   
    { [ MARK_IN_USE_FOR_REMOVAL ] , [ NO_INFOMSGS ]  }  
    ]  
( 'ALL' [,pool_name ] )
ALL specifies all supported caches.
pool_name specifies a Resource Governor pool cache. Only entries associated with this pool are freed.
MARK_IN_USE_FOR_REMOVAL
Asynchronously frees currently used entries from their respective caches after they're unused. After the DBCC FREESYSTEMCACHE WITH MARK_IN_USE_FOR_REMOVAL runs, new entries created in the cache aren't affected.
NO_INFOMSGS
Suppresses all informational messages.
Example:
Releasing unused cache entries from a Resource Governor pool cache
DBCC FREESYSTEMCACHE ('ALL', default);

Releasing entries from their respective caches after they become unused
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

DBCC CLONEDATABASE

Generates a schema-only clone of a database by using DBCC CLONEDATABASE in order to investigate performance issues related to the query optimizer.

Syntax:
DBCC CLONEDATABASE   
(  
    source_database_name
    ,  target_database_name
)
    [ WITH { [ NO_STATISTICS ] [ , NO_QUERYSTORE ] [ , VERIFY_CLONEDB | SERVICEBROKER ] [ , BACKUP_CLONEDB ] } ] 

source_database_name
The name of the database to be copied.
target_database_name
The name of the database the source database will be copied to. This database will be created by DBCC CLONEDATABASE and shouldn't already exist.
NO_STATISTICS
Specifies if table/index statistics need to be excluded from the clone. If this option is not specified, table/index statistics are automatically included. This option is available starting with SQL Server 2014 (12.x) SP2 CU3 and SQL Server 2016 (13.x) SP1.
NO_QUERYSTORE
Specifies if query store data needs to be excluded from the clone. If this option is not specified, query store data will be copied to the clone if the query store is enabled in the source database. This option is available starting with SQL Server 2016 (13.x) SP1.
VERIFY_CLONEDB
Verifies the consistency of the new database. This option is required if the cloned database is intended for production use. Enabling VERIFY_CLONEDB also disables statistics and query store collection, thus it is equivalent to running WITH VERIFY_CLONEDB, NO_STATISTICS, NO_QUERYSTORE. This option is available starting with SQL Server 2014 (12.x) SP3, SQL Server 2016 (13.x) SP2, and SQL Server 2017 (14.x) CU8.
SERVICEBROKER
Specifies if service broker related system catalogs should be included in the clone. The SERVICEBROKER option cannot be used in combination with VERIFY_CLONEDB. This option is available starting with SQL Server 2014 (12.x) SP3, SQL Server 2016 (13.x) SP2, and SQL Server 2017 (14.x) CU8.
BACKUP_CLONEDB
Creates and verifies a backup of the clone database. If used in combination with VERIFY_CLONEDB, the clone database is verified before the backup is taken. This option is available starting with SQL Server 2014 (12.x) SP3, SQL Server 2016 (13.x) SP2, and SQL Server 2017 (14.x) CU8.
Example:
Creating a clone of a database that includes schema, statistics and query store
DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone);    
GO 

Creating a schema-only clone of a database without statistics
DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone) WITH NO_STATISTICS;    
GO

Creating a schema-only clone of a database without statistics and query store
DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone) WITH NO_STATISTICS, NO_QUERYSTORE;    
GO 

Creating a clone of a database that is verified for production use
DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone) WITH VERIFY_CLONEDB;    
GO 

Creating a clone of a database that is verified for production use that includes a backup of the cloned database
DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone) WITH VERIFY_CLONEDB, BACKUP_CLONEDB;    
GO 

Informational Statements

DBCC INPUTBUFFER

Displays the last statement sent from a client to an instance of Microsoft SQL Server.

Syntax:
DBCC INPUTBUFFER ( session_id [ , request_id ])  
[WITH NO_INFOMSGS ]
Example:
CREATE TABLE dbo.T1 (Col1 int, Col2 char(3));  
GO  
DECLARE @i int = 0;  
BEGIN TRAN  
SET @i = 0;  
WHILE (@i < 100000)  
BEGIN  
INSERT INTO dbo.T1 VALUES (@i, CAST(@i AS char(3)));  
SET @i += 1;  
END;  
COMMIT TRAN;  
--Start new connection #2.  
DBCC INPUTBUFFER (52); 

DBCC SHOWCONTIG

Displays 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:
Displaying fragmentation information for a table
USE AdventureWorks2012;  
GO  
DBCC SHOWCONTIG ('HumanResources.Employee');  
GO  

Displaying an abbreviated result set for a table
USE AdventureWorks2012;  
GO  
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;  
GO  

Displaying the full result set for every index on every table in a database
USE AdventureWorks2012;  
GO  
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;  
GO  

DBCC OPENTRAN

DBCC OPENTRAN helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database. Results are displayed only if there is an active transaction that exists in the log or if the database contains replication information. An informational message is displayed if there are no active transactions in the log.

Syntax:
DBCC OPENTRAN   
[   
    ( [ database_name | database_id | 0 ] )   
    { [ WITH TABLERESULTS ]  
      [ , [ NO_INFOMSGS ] ]  
    }  
Example:
Returning the oldest active transaction
CREATE TABLE T1(Col1 int, Col2 char(3));  
GO  
BEGIN TRAN  
INSERT INTO T1 VALUES (101, 'abc');  
GO  
DBCC OPENTRAN;  
ROLLBACK TRAN;  
GO  
DROP TABLE T1;  
GO  

Specifying the WITH TABLERESULTS option
-- Create the temporary table to accept the results.  
CREATE TABLE #OpenTranStatus (  
   ActiveTransaction varchar(25),  
   Details sql_variant   
   );  
-- Execute the command, putting the results in the table.  
INSERT INTO #OpenTranStatus   
   EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');  
  
-- Display the results.  
SELECT * FROM #OpenTranStatus;  
GO

DBCC OUTPUTBUFFER

Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.

Syntax:
DBCC OUTPUTBUFFER ( session_id [ , request_id ])  
[ WITH NO_INFOMSGS ] 
Example:
SELECT request_id   
FROM sys.dm_exec_requests   
WHERE session_id = @@spid;

DBCC OUTPUTBUFFER (52); 

DBCC PROCCACHE

Displays information in a table format about the procedure cache.

Syntax:
DBCC PROCCACHE [ WITH NO_INFOMSGS ]

WITH
Allows for options to be specified.
NO_INFOMSGS
Suppresses all informational messages that have severity levels 0 through 10.

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. For example, the Query Optimizer could use cardinality estimates to choose the index seek operator instead of the index scan operator in the query plan, improving query performance by avoiding a resource-intensive index scan.

DBCC SHOW_STATISTICS displays the header, histogram, and density vector based on data stored in the statistics object. The syntax lets you specify a table or indexed view along with a target index name, statistics name, or column name. This topic describes how to display the statistics and how to understand the displayed results.

Syntax:
DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )   
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]  
< option > :: =  
    STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM
Example:
Returning all statistics information
DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid);  
GO

Specifying the HISTOGRAM option
DBCC SHOW_STATISTICS ("dbo.DimCustomer",Customer_LastName) WITH HISTOGRAM;  
GO 

DBCC SQLPERF

Provides transaction log space usage statistics for all databases. In SQL Server 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);  
GO 

DBCC TRACESTATUS

Displays the status of trace flags.

Syntax:
DBCC TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] )   
[ WITH NO_INFOMSGS ]
Example:
The following example displays the status of all trace flags that are currently enabled globally.
DBCC TRACESTATUS(-1);  
GO  

The following example displays the status of trace flags 2528 and 3205.
DBCC TRACESTATUS (2528, 3205);  
GO

The following example displays whether trace flag 3205 is enabled globally.
DBCC TRACESTATUS (3205, -1);  
GO 

The following example lists all the trace flags that are enabled for the current session.
DBCC TRACESTATUS();  
GO  

DBCC USEROPTIONS

Returns the SET options active (set) for the current connection.

Syntax:
DBCC USEROPTIONS  
[ WITH NO_INFOMSGS ]
Example:
DBCC USEROPTIONS; 

Validation Statements

DBCC CHECKALLOC

Checks 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 AdventureWorks2012 database.  
DBCC CHECKALLOC (AdventureWorks2012);  
GO

DBCC CHECKCATALOG

Checks for catalog consistency within the specified database. The database must be online.

Syntax:
DBCC CHECKCATALOG   
[   
    (   
    database_name | database_id | 0  
    )  
]  
    [ WITH NO_INFOMSGS ]
Example:
The following example checks the catalog integrity in both the current database and in the AdventureWorks database.
-- Check the current database.  
DBCC CHECKCATALOG;  
GO  
-- Check the AdventureWorks2012 database.  
DBCC CHECKCATALOG (AdventureWorks2012);  
GO

DBCC CHECKCONSTRAINTS

Checks 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:
Checking all enabled and disabled constraints on all tables
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;  
GO

Checking a specific constraint
USE AdventureWorks2012;  
GO  
DBCC CHECKCONSTRAINTS ('Production.CK_ProductCostHistory_EndDate');  
GO  

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.
  • Validates the Service Broker data in the database.

This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB. For more detailed information about the checks that these commands perform, see the descriptions of these commands.

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 } ]    
            [ , MAXDOP  = number_of_processors ]    
        }    
    ]    
]    
Example:
Checking both the current and another database
-- Check the current database.    
DBCC CHECKDB;    
GO    
-- Check the AdventureWorks2012 database without nonclustered indexes.    
DBCC CHECKDB (AdventureWorks2012, NOINDEX);    
GO  

Checking the current database, suppressing informational messages
DBCC CHECKDB WITH NO_INFOMSGS;    
GO 

DBCC CHECKFILEGROUP

Checks 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 ]    
            [ , MAXDOP  = number_of_processors ]  
        }   
    ]  
]  
Example:
Checking the PRIMARY filegroup in the a database
DBCC CHECKFILEGROUP;  
GO  

Checking the AdventureWorks PRIMARY filegroup without nonclustered indexes
USE AdventureWorks2012;  
GO  
DBCC CHECKFILEGROUP (1, NOINDEX);  
GO 

Checking the PRIMARY filegroup with options
USE master;  
GO  
DBCC CHECKFILEGROUP (1)  
WITH ESTIMATEONLY;

DBCC CHECKIDENT

Checks the current identity value for the specified table in SQL Server 2019 (15.x) and, if it’s 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:
Resetting the current identity value, if it's needed
USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType');  
GO

Reporting the current identity value
USE AdventureWorks2012;
GO  
DBCC CHECKIDENT ('Person.AddressType', NORESEED);
GO  

Forcing the current identity value to a new value
USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);  
GO  

Resetting the identity value on an empty table
USE AdventureWorks2012;  
GO  
TRUNCATE TABLE dbo.ErrorLog
GO
DBCC CHECKIDENT ('dbo.ErrorLog', RESEED, 1);  
GO  

DBCC CHECKTABLE

Checks 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 } ]     
          [ , MAXDOP = number_of_processors ]    
        }    
    ] 
Example:
Checking a specific table
DBCC CHECKTABLE ('HumanResources.Employee');    
GO 

Performing a low-overhead check of the table
DBCC CHECKTABLE ('HumanResources.Employee') WITH PHYSICAL_ONLY;    
GO

Checking a specific index
DECLARE @indid int;    
SET @indid = (SELECT index_id     
              FROM sys.indexes    
              WHERE object_id = OBJECT_ID('Production.Product')    
                    AND name = 'AK_Product_Name');    
DBCC CHECKTABLE ('Production.Product',@indid);  

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply