DBCC commands:
1. DBCC means Database consistency checker
2. It is used for DB troubleshooting and performance checking

The Permission Requirement:
1. “sysadmin” or “db_owner” ,ust have permission to run DBCC
2. Each DBCC commands will have additional permission requirement

DBCC commands have 4 types:
1. Maintenance: Maintenance tasks such as table cleaning, buffer cleaning, DB shrinking etc…
2. Micscellaneous: Miscellaneous tasks such as unload DLL, empty DB authentication cache, release unused cache entries etc…
3. Informational: Gather and display the last statement, the oldest active transaction transactions, procedure cache etc…
4. Validation: Validate the consistency of disk space, consistency of catalog, integrity of all obejcts in DB etc…

Maintenance type of DBCC
1. DBCC CLEANTABLE
a. We can use it after table have significant changeing on variable-length columns
b. It need permission of db_owner or db_ddladmin

DBCC CLEARNTABLE (<db_name>, '<table_name>',<process_now_number>);
-- process_now_number> is the number of row processed per transaction.

-- If <process_now_number> does not specified or is zero, it process the whole
-- table in one transaction

2. DBCC DROPCLEANBUFFERS
a. It removes all clean buffers from the buffer pool
b. It removes all columnstore objects from the columnstore object pool
c. If it work with a cold buffer cache, we need to first run ‘CHECKPOINT’ to produce a cold buffer cache

DBCC DROPCLEANBUFFERS

3. DBCC FREEPROCCACHE
a. It will cause all procedure to be evicted
b. Incoming query executions will compile a new prcedure
c. It will casue a sudden, temporary decrease in query performance

DBCC FREEPROCCACHE ( <plan_handle> | <sql_handle> | <pool_name> )
-- <plan_handle> can be obtained from sys.dm_exec_cached_plans,
-- sys.dm_exec_request, sys.dm_exec_query_memory_grants &
-- sys.dm_exec_query_stats

-- <sql_handle> can be obtained from sys.dm_exec_query_stats,
-- sys.dm_exec_requests, sys.dm_exec_cursors, sys.dm_exec_xml_handles &
-- sys.dm_exec_query_memory_grants

-- <pool_name> can be obtained from sys.dm_resource_governor_resource_pools

4. DBCC SHRINKDATABASE
a. It shrinks the size of the data & log files
b. AzureSQL Data Warehouse does not support it
c. It will cause data warehouse offline
d. It should not run repeatedly
e. It should run after the operation of creates unused space, such as a truncate table or a drop table operation

DBCC SHRINKDATABASE (<DB_name>,<free_space%>)
-- <free_space%> is the % of free space that you want left in the database file

5. DBCC UPDATEUSAGE
a. It reports and correct pages and row count inaccuracies
b. It should not run routinely
c. It should run only we suspect incorrect values are being returned by sp_spaceused

DBCC UPDATEUSAGE (0)
-- Update current DB

DBCC UPDATEUSAGE (<DB_name>, <table_name>)
-- Update specific table

DBCC UPDATEUSAGE (<DB_name>, <table_name>, <index_name>)
-- Update specific index

Micscellaneous type of DBCC
1. DBCC <dll_name> (FREE)
a. It can unload the specified extended stored procedure DLL from memory
b. “sp_helpextendedproc” list a current loaded DLL

DBCC <dll_name> (FREE)

2. DBCC FLUSHAUTHCACHE
a. It is only available for Azure SQL DB
b. It empties the DB authentication cache containg information about login and firewall rules

DBCC FLUSHAUTHCACHE

3. DBCC FREESESSIONCACHE
a. It flushes the distributed query connection cache

DBCC FREESESSIONCACHE

4. DBCC FREESYSTEMCACHE
a. It releases all unused cache entries from all caches

BCC FREESYSTEMCACHE('ALL');

5. DBCC TRACEOFF
a. It is used for disable specific trace flag

DBCC TRACEOFF(<trace_id>);
--Using "SELECT * FROM ::fn_trace_getinfo(default)" to view all existing traces

6. DBCC TRACEON
a. It is used for enable specific trace flag

DBCC TRACEON(<trace_id>);
--Using "SELECT * FROM ::fn_trace_getinfo(default)" to view all existing traces

7. DBCC CLONEDATABASE
a. It generates a schema-only clone of a DB

DBCC CLONEDATABASE('<source_db_name>','<target_db_name>')

Informational type of DBCC
1. DBCC INPUTBUFFER
a. It displays the last statement sent from a client

DBCC INPUTBUFFER(<session_id>)
-- <session_id> can be found by SPID cloumn in 'sp_who2'

2. DBCC OPENTRAN
a. It displays the oldest active transaction & the oldest distributed and non-distributed replicated transactions

DBCC OPENTRAN(<db_name>)
-- If no <db_name> specific or set to 0, the current DB is used

3. DBCC OUTPUTBUFFER
a. It displays the current output buffer in hexadecimal & ASCII format for the specificed session_id

DBCC OUTPUTBUFFER(<session_id>)
--<session_id> can be found by SPID cloumn in 'sp_who2'

4. DBCC PROCCACHE
a. It displays information in a table format about the procedue cache

DBCC PROCCACHE

5. DBCC SHOW_STATISTICS
a. It displays current query optimization statistics for a table or indexed

DBCC SHOW_STATISTICS(<table_name>,<index_name|column_name>)

6. DBCC SHOWCONFIG
a. It will be removed in a future
b. Use ‘sys.dm_db_index_physical_stats’ instead

7. DBCC SQLPERF
a. Use ‘sys.dm_db_log_space_usage’ instead

8. DBCC TRACESTATUS
a. It lists the trace flag that are enabled for the seesion
b. List ALL — DBCC TRACESTATUS()

DBCC TRACESTATUS(traceID_1, traceID_2)
-- List traceID_1,traceID_2

9. DBCC USEROPTIONS
a. It returns the SET options active (set) for the current connection
b. When ‘READ_COMMITED_SNAPSHOT’=NO & transaction isolation level = ‘read committed’, the isolation level will reports as ‘read committed snapshot’

DBCC USEROPTIONS;

Vaildation type of DBCC
1. DBCC CHECKALLOC
a. It checks the consistency of disk space allocation structures for a specificed DB
b. It uses an internal DB snapshot to provide the transactional consistency
c. It does not check ‘TEMP’ DB
d. If the DBCC command stop becase of an error, it return the message which include a state value, and the amount of time the command ran.
I. State 0: Error number 8930 was rasied. This indicates a corruption in metadata that terminated the DBCC command
II. State 1: Error number 8967 was raised. There was an internal DBCC error
III. State 2: A failure occurred during emergency mode database repair.
IV. State 3: This indicates a corruption in metadata that terminated the DBCC command.
V. State 4: An assert or access violation was detected.
VI. State 5: An unknown error occurred that terminated the DBCC command.

DBCC CHECKALLOC
-- check the current DB

DBCC CHECKALLOC(<db_name>)
-- check specific DB

2. DBCC CHECKCATALOG
a. It checks catalog consistency within the specific DB
d. If the DBCC command stop becase of an error, it return the message which include a state value, and the amount of time the command ran.
I. State 0: Error number 8930 was rasied. This indicates a corruption in metadata that terminated the DBCC command
II. State 1: Error number 8967 was raised. There was an internal DBCC error
III. State 2: A failure occurred during emergency mode database repair.
IV. State 3: This indicates a corruption in metadata that terminated the DBCC command.
V. State 4: An assert or access violation was detected.
VI. State 5: An unknown error occurred that terminated the DBCC command.

DBCC CHECKCATALOG

3. DBCC CHECKCONSTRAINTS
a. It checks the integrity of a specificed constraints or all constraints on a specificied table

DBCC CHECKCONSTRAINTS(<table_name>)
-- check specific table

DBCC CHECKCONSTRAINTS(<constraint_name>)
-- check specific constraint

4. DBCC CHECKDB
a. It checks the logical & physical integrity of all objects in the specified DB
b. It constains multi checking.
I. DBCC CHECKAALOC on every tables
II. DBCC CHECKTABLE on every tables and views
III. DBCC CHECKCATALOG on DB
IV. Validate all indexed view
V. Validate link-level consistency between table metadata & file system directories & files

DBCC CHECKDB PHYSICAL_ONLY
-- can frequentuse on production systems

DBCC CHECKDB
-- can periodically use on production systems

5. DBCC CHECKFILEGROUP
a. It checks the alloction & structural integrity of all tables and indexed viewss in the specificed filegroups
b. It limited to the single specificed filegroup & required tables

DBCC CHECKFILEGROUP
-- check primary filegroup of the current DB

DBCC CHECKFILEGROUP(<filegroup_id>)
-- check specific filegroup of the current DB

6. DBCC CHECKIDENT
a. It checks or modify the current identity value for the specificed table

DBCC CHECKIDENT(<table_name>, NORESEED)
-- return current identity value & current maximum value of the identity
-- column_name

DBCC CHECKIDENT(<table_name>, RESEED)
-- If current identity value for a table is less than the maximum identity value
-- stored in the identity column, it reset using the maximum value in the
-- identity column

DBCC CHECKIDENT(<table_name>, RESEED, <new_resend_value>)
-- Current identity value is set to <new_resend_value>

-- If all rows have been removed by "TRUNCATE TABLE", the first row
-- inserted uses <new_resend_value>

-- If all rows have been removed by "DELETE", the first row insert uses
-- <new_resend_value> + the current increment value

-- If the column is PRIMARY KEY or UNIQUE, error message 2627 will
-- be generated

-- If the column is not PRIMARY KEY or UNIQUE, the duplicate identity
-- values will existed

7. DBCC CHECKTABLE
a. It checks the integrity of all the pages and structures that make up the table or indexed view

DBCC CHECKTABLE('<table_name>')