SQL Server : Database Console Commands (DBCC)

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: