Here are some examples of Database Console Commands (DBCC).
-- Like flush of buffer cache.
dbcc DropCleanBuffers
-- Shrink all files in the database.
-- Parameters: Database name and percentage to leave free.
-- *** You should probably never do this! ***
dbcc ShrinkDatabase(N'MyDatabase', 10)
-- List files.
sp_helpfile
-- Shrink a specific file.
-- Parameters: File name and final size of file in MB.
-- Shrink will not reduce size below the contents of the files. There is no loss of data possible.
-- Remember, shrinks are moving pages around, so they can cause index fragmentation.
-- After a large shrink you should consider index rebuilds.
-- *** You should probably never do this! ***
dbcc ShrinkFile(N'mastlog', 5)
-- Shrink a log file for a database in FULL recovery mode.
-- This is something quite drastic, so don't do it on a regular basis.
-- The size of the log files should be controlled by frequent transaction log backups.
USE MyDatabase
ALTER DATABASE MyDatabase
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (N'MyDatabase_log', 100)
GO
ALTER DATABASE MyDatabase
SET RECOVERY FULL
-- Do a full backup.
-- Like flush shared pool.
dbcc FreeProcCache
-- Shrink tempdb
USE [tempdb]
GO
DBCC SHRINKDATABASE(N'tempdb' )
GO
-- Check whole database. Two methods.
dbcc CheckDB(N'MyDatabase')
use MyDatabase
dbcc CheckDB
-- Repair: Message from check will instruct correct mode.
alter database MyDatabase set single_user
dbcc CheckDB(N'MyDatabase', repair_allow_data_loss)
alter database MyDatabase set multi_user
-- Check individual table.
use MyDatabase
dbcc CheckTable(employees)
-- Check the size of the logs and tht % used.
-- May want to shrink if they are very big and empty from one-off operation.
dbcc SQLPerf(logspace)
-- Last query ran.
-- Check for blocking sessions in blocked column.
-- Use InputBuffer against the blocked and blocking SPID to see the last statement they ran.
SELECT * FROM sys.sysprocesses
dbcc InputBuffer(spid)
dbcc InputBuffer(8)
-- Which commands can you get help on?
dbcc help ('?')
-- Get help on a specific DBCC command.
dbcc help ('cleantable')
For more information see: