Microsoft SQL Server DBCC maintenance commands


Example

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 (0x060006001ECA270EC0215D05000000000000000000000000);

Removes all SQL query in plan cache. Every new plan will be recompiled: You can specify plan handle, query handle to clean plans for the specific query plan or SQL statement.

DBCC FREESYSTEMCACHE ('ALL', myresourcepool); 
-- or
DBCC FREESYSTEMCACHE;

Cleans all cached entries created by system. It can clean entries o=in all or some specified resource pool (myresourcepool in the example above)

DBCC FLUSHAUTHCACHE 

Empties the database authentication cache containing information about logins and firewall rules.

DBCC SHRINKDATABASE (MyDB [, 10]); 

Shrinks database MyDB to 10%. Second parameter is optional. You can use database id instead of name.

DBCC SHRINKFILE (DataFile1, 7); 

Shrinks data file named DataFile1 in the current database. Target size is 7 MB (tis parameter is optional).

DBCC CLEANTABLE (AdventureWorks2012,'Production.Document', 0) 

Reclaims a space from specified table