DBCC commands

MSSQL-Feature-Image

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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()

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’

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.

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.

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

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

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

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

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

Author: Joe Chan