Tutorial by Examples

DBCC commands enable user to maintain space in database, clean caches, shrink databases and tables. Examples are: DBCC DROPCLEANBUFFERS Removes all clean buffers from the buffer pool, and columnstore objects from the columnstore object pool. DBCC FREEPROCCACHE -- or DBCC FREEPROCCACHE (0x06...

DBCC commands enable user to validate state of database. ALTER TABLE Table1 WITH NOCHECK ADD CONSTRAINT chkTab1 CHECK (Col1 > 100); GO DBCC CHECKCONSTRAINTS(Table1); --OR DBCC CHECKCONSTRAINTS ('Table1.chkTable1'); Check constraint is added with nocheck options, so it will not be ...

DBCC commands can show information about database objects. DBCC PROCCACHE Displays information in a table format about the procedure cache. DBCC OUTPUTBUFFER ( session_id [ , request_id ]) Returns the current output buffer in hexadecimal and ASCII format for the specified session_id (and o...

Trace flags in SQL Server are used to modify behavior of SQL server, turn on/off some features. DBCC commands can control trace flags: The following example switches on trace flag 3205 globally and 3206 for the current session: DBCC TRACEON (3205, -1); DBCC TRACEON (3206); The following examp...

DBCC statements act as Database Console Commands for SQL Server. To get the syntax information for the specified DBCC command use DBCC HELP (...) statement. The following example returns all DBCC statements for which Help is available: DBCC HELP ('?'); The following example returns options f...