DBCC COMMANDS

DBCC CHECKALLOC - Check consistency of disk allocation.
DBCC CHECKCATALOG - Check catalog consistency
DBCC CHECKCONSTRAINTS - Check integrity of table constraints.
DBCC CHECKDB - Check allocation, and integrity of all objects.
DBCC CHECKFILEGROUP - Check all tables and indexed views in a filegroup.
DBCC CHECKIDENT - Check identity value for a table.
DBCC CHECKTABLE - Check integrity of a table or indexed view.
DBCC CLEANTABLE - Reclaim space from dropped variable-length columns.
DBCC dllname - Unload a DLL from memory.
DBCC DROPCLEANBUFFERS - Remove all clean buffers from the buffer pool.
DBCC FREE... CACHE - Remove items from cache.
DBCC HELP - Help for DBCC commands.
DBCC INPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC OPENTRAN - Display information about recent transactions.
DBCC OUTPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC PROCCACHE - Display information about the procedure cache
DBCC SHOW_STATISTICS - Display the current distribution statistics
DBCC SHRINKDATABASE - Shrink the size of the database data and log files.
DBCC SHRINKFILE - Shrink or empty a database data or log file.
DBCC SQLPERF - Display transaction-log space statistics. Reset wait and latch statistics.
DBCC TRACE... - Enable or Disable trace flags
DBCC UPDATEUSAGE - Report and correct page and row count inaccuracies in catalog views
DBCC USEROPTIONS - Return the SET options currently active
DBCC deprecated commands
DBCC Undocumented commands


1.DBCC CHECKALLOC - Check the consistency of disk space allocation.

Syntax
DBCC CHECKALLOC [ ( 'database' | database_id | 0
[ , NOINDEX | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
[WITH
{ [ ALL_ERRORMSGS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
}
]
]

Key:
REPAIR_FAST | REPAIR_REBUILD | NOINDEX - deprecated options
REPAIR_ALLOW_DATA_LOSS - Use only as a last resort
- back up the database before you run this option.To find the repair level to use run DBCC CHECKDB without a repair option. The best and easiest way to repair errors is to restore from a backup.

Examples-- Check disc space allocation for the current database.DBCC CHECKALLOC;GO
"Millions of individuals making their own decisions in the market-place will always allocate resources better than any centralized government planning process" - Ronald W. Reagan

Related

DBCC CHECKDB - Check allocation, and integrity of all objects.

Equivalent Oracle commands:

ALTER TABLE MODIFY CONSTRAINT VALIDATE
DBMS_REPAIR


2. DBCC CHECKCATALOG - Check for catalog consistency

Syntax
DBCC CHECKCATALOG
[('database_name' | database_id | 0 )]
[WITH NO_INFOMSGS]

Key:
WITH NO_INFOMSGS - Suppresses all informational messages.
"The quality of a department is inversely proportional to the number of courses it lists in its catalogue" - Hildebrand's Law

Related

DBCC CHECKTABLE - Check integrity of a table or indexed view.


3. DBCC CHECKCONSTRAINTS - Check the integrity of table constraints.
Syntax
DBCC CHECKCONSTRAINTS
[('table' | table_id | 'constraint' | constraint_id) ]
[WITH
[ { ALL_CONSTRAINTS | ALL_ERRORMSGS } ]
[ , ] [ NO_INFOMSGS ]
]
Key:
ALL_CONSTRAINTS - Check disabled constraints in addition to enabled constraints
has no effect when a constraint name is specified.
ALL_ERRORMSGS - Return all rows that violate constraints in the table being checked.
default = first 200 rows.
"The practice of both mathematics and art requires a blend of discipline and vision--a delicate balancing of constraint and freedom" - Ivars Peterson

Related

DBCC CHECKTABLE - Check integrity of a table or indexed view.

Equivalent Oracle commands:

ALTER TABLE MODIFY CONSTRAINT VALIDATE


4. DBCC CHECKDB - Check the allocation, and integrity of all objects in a database.

Syntax
DBCC CHECKDB [( 'database' | database_id | 0 [ , NOINDEX {REPAIR_ALLOW_DATA_LOSS |
REPAIR_FAST |
REPAIR_REBUILD} )] [WITH { [ALL_ERRORMSGS ] [ , NO_INFOMSGS ] [ , TABLOCK ] [, ESTIMATEONLY ] [ , { PHYSICAL_ONLY | DATA_PURITY } ] } ]
Key:
NOINDEX - Skip intensive checks of nonclustered indexes for user tables
REPAIR_ALLOW_DATA_LOSS - Try to repair all reported errors.
REPAIR_REBUILD - Perform all repairs that can be performed without risk of data loss.
ALL_ERRORMSGS - Return all reported errors per object, default = first 200 errors.
TABLOCK - Obtain locks instead of using an internal database snapshot.
this limits the checks that are performed.
ESTIMATEONLY - Display the estimated amount of tempdb space that would be required.
PHYSICAL_ONLY - Limits the checking to the integrity of the physical structure
DATA_PURITY - Check the database for column values that are not valid or out-of-range.
Example

-- Check the current database.DBCC CHECKDB;GO


5. DBCC CHECKFILEGROUP - Check the allocation and structural integrity of all tables and indexed views in a filegroup.
Syntax
DBCC CHECKFILEGROUP
[( 'filegroup' | filegroup_id | 0
[, NOINDEX ]
)]
[WITH
[ {ALL_ERRORMSGS | NO_INFOMSGS } ]
[, TABLOCK ] [ , ESTIMATEONLY ] [ , PHYSICAL_ONLY ]
]
Key:
filegroup_name - The name of the filegroup to be checked.
default (or if 0 is specified) = the primary filegroup.
NOINDEX - Skip intensive checks of nonclustered indexes.
ALL_ERRORMSGS - Return all reported errors per object, default = first 200 errors.
TABLOCK - Obtain locks instead of using an internal database snapshot.
ESTIMATEONLY - Display the estimated amount of tempdb space that would be required.
PHYSICAL_ONLY - Limit checking to the integrity of the physical structure of the page,
record headers and the physical structure of B-trees.Examples

-- Check the primary filegroup in 'MyDatabase'
USE MyDatabase;GODBCC CHECKFILEGROUP;GO
"One of the most important ways to manifest integrity is to be loyal to those who are not present. In doing so, we build the trust of those who
are present" - Stephen Covey

Related

DBCC CHECKTABLE - Check integrity of a table or indexed view.


6. DBCC CHECKIDENT - Check and/or reseed the current identity value for a table.

Syntax
DBCC CHECKIDENT ( 'table' [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ] ) [WITH NO_INFOMSGS]

Key:
NORESEED - The current identity value should not be changed.
RESEED - Change the identity value.
new_reseed_value - The new seed value to be used for the identity column.
WITH NO_INFOMSGS - Suppresses all information messages.Example

-- Reset the current identity value
USE MyDatabase;GODBCC CHECKIDENT ('MySchema.MyTable', RESEED, 5000);GO
I finally got it all together and now I forgot where I put it!

Related commands

DBCC CHECKCONSTRAINTS - Check integrity of table constraints.

Equivalent Oracle command:

SELECT Sequence_for_my_Table.currval into CurrIdentity from dual;




7. DBCC CHECKTABLE - Check the integrity of a table or indexed view.
Syntax
DBCC CHECKTABLE ('table' | 'view' [ , { NOINDEX | index_id } |, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ) [WITH { ALL_ERRORMSGS ] [ , EXTENDED_LOGICAL_CHECKS ] [ , NO_INFOMSGS ] [ , TABLOCK ] [ , ESTIMATEONLY ] [ , { PHYSICAL_ONLY | DATA_PURITY } ] } ]
Key:
NOINDEX - Skip intensive checks of nonclustered indexes.
REPAIR_ALLOW_DATA_LOSS - Try to repair all reported errors.
REPAIR_REBUILD - Perform all repairs that can be performed without risk of data loss.
REPAIR_FAST - deprecated option
ALL_ERRORMSGS - Return all reported errors per object, default = first 200 errors.
TABLOCK - Obtain locks instead of using an internal database snapshot.
ESTIMATEONLY - Display the estimated amount of tempdb space that would be required.
PHYSICAL_ONLY - Limits the checking to the integrity of the physical structure
EXTENDED_LOGICAL_CHECKS - If the compatibility level is 100 (SQL Server 2008) or higher,
perform logical consistency checks on indexed views, XML indexes,
and spatial indexes.
DATA_PURITY - Check the table for column values that are not valid or out-of-range.Use the DATA_PURITY option for databases upgraded from versions of SQL Server earlier than 2005.

To repair errors restore from a backup, use the REPAIR options only as a last resort.

Examples

-- Check the integrity of MyTable in 'MyDatabase'
USE MyDatabase;GODBCC CHECKTABLE ('MySchema.MyTable')GO"We're going to rebuild and rebuild stronger" - Mayor Rudolph Giuliani

Related

DBCC CHECKCONSTRAINTS - Check integrity of table constraints.



8. DBCC CLEANTABLE
Reclaim space from dropped variable-length columns in a table or indexed view.

Syntax
DBCC CLEANTABLE
( { 'database' | database_id | 0 }
, { 'table' | table_id | 'view' | view_id }
[ , batch_size]
) [WITH NO_INFOMSGS]

Key:
batch_size - The no. of rows to process per transaction.
default (or if 0 is specified) = whole table
NO_INFOMSGS - Suppress all information messages.
If 0 is specified, the current database will be used.

Example

DBCC CLEANTABLE ('MyDatabase','MySchema.MyTable', 0)WITH NO_INFOMSGS;GO"It's hard to be funny when you have to be clean" - Mae West.

Equivalent Oracle command:

ALTER TABLESPACE COALESCE



9. DBCC dllname

Unload a DLL from memory.

Syntax
DBCC dllname (FREE) [ WITH NO_INFOMSGS ]


Key:
dllname - Name of the DLL to release from memory.
WITH NO_INFOMSGS - Suppress all information messages.

When an extended stored procedure is executed, the DLL will remain loaded until the server is shut down (or DBCC dllname is used to unload it)

Example

DBCC xp_my_stored_proc (FREE)"...there is nothing that so much contributes to a survival of the trials and sufferings of the day as a sense of humor. It is like the buffers in
the solid train, like the air cushion of a modern field gun. It saves the jolt; it takes up the recoil" - William Howard Taft

Equivalent Oracle command:

Set (for all packages) with Server Parameters..
SHARED_POOL_SIZE / LARGE_POOL_SIZE / JAVA_MAX_SESSIONSPACE_SIZE



10. DBCC DROPCLEANBUFFERS
Remove all clean buffers from the buffer pool.

Syntax
DBCC DROPCLEANBUFFERS [WITH NO_INFOMSGS]


Key:
WITH NO_INFOMSGS - Suppress all information messages.

This command is useful to test queries with a cold buffer cache without shutting down and restarting the server.

To produce a 'cold' buffer cache with all dirty pages written to disk, first use CHECKPOINT.

# Why can't I ease your doubtful mind, and melt your cold, cold heart # - Hank Williams

Related commands:

CHECKPOINT
DBCC FREE... CACHE

Equivalent Oracle command:

ALTER SYSTEM FLUSH SHARED POOL



11. DBCC FREE... CACHE
DBCC FREEPROCCACHE - Remove all elements from the procedure cache.
DBCC FREESESSIONCACHE - Flush the distributed query connection cache.
DBCC FREESYSTEMCACHE - Release all unused cache entries from all caches.

Syntax
DBCC FREEPROCCACHE [WITH NO_INFOMSGS]

DBCC FREESESSIONCACHE [WITH NO_INFOMSGS]

DBCC FREESYSTEMCACHE
('ALL')
[WITH [MARK_IN_USE_FOR_REMOVAL] , [NO_INFOMSGS] ]

Key:
NO_INFOMSGS - Suppress all information messages
ALL - All supported caches
MARK_IN_USE_FOR_REMOVAL - Free up currently used entries asap (asynchronous)
MARK_IN_USE_FOR_REMOVAL will not prevent new entries being created in the cache.
Requires ALTER SERVER STATE permission on the server.

Examples

USE MyDatabase;GODBCC FREESESSIONCACHE WITH NO_INFOMSGS;GO

DBCC FREEPROCCACHE;"It's hard to be funny when you have to be clean" - Mae West.

Related commands:

DBCC DROPCLEANBUFFERS

Equivalent Oracle command:

ALTER SYSTEM FLUSH SHARED POOL


12. DBCC HELP
Help and syntax information for DBCC commands.

Syntax
DBCC HELP ('dbcc_command') [WITH NO_INFOMSGS ]

DBCC HELP (@dbcc_command_var) [WITH NO_INFOMSGS ]

DBCC HELP ('?') [WITH NO_INFOMSGS ]

Key:
WITH NO_INFOMSGS - Suppress all information messages (severity levels 0-10)
dbcc_command - The DBCC 'xyz' commandExamples

DBCC HELP ('?')GO

DBCC HELP ('USEROPTIONS')
GO

DECLARE @help_var sysname
SET @help_var = 'CHECKALLOC'
DBCC HELP (@help_var)
GO"It is our special duty, that if anyone needs our help, we should give him such help to the utmost of our power" - Cicero

Equivalent Oracle command:

HELP topic - In SQL*Plus



13. DBCC INPUTBUFFER / OUTPUTBUFFER

Display the last statement sent from a client to a database instance.

Syntax
DBCC INPUTBUFFER (session_id [, request_id ]) [WITH NO_INFOMSGS ]

DBCC OUTPUTBUFFER (session_id [, request_id ]) [WITH NO_INFOMSGS ]

Key:
session_id - The session ID
request_id - A specific request (batch) within the session.
NO_INFOMSGS - Suppress all information messages (severity 0-10)To find the request_ids for a given session id (@@spid = current session):

SELECT request_id
FROM sys.dm_exec_requests
WHERE session_id = @@spid

Example

-- Display session # 52DBCC INPUTBUFFER (52);
DBCC OUTPUTBUFFER (52); "A lawyer who represents himself has a fool for a client." - proverb

Equivalent Oracle command:

Select from v$SESSION


14. DBCC OPENTRAN
Display information about the oldest active transaction and the oldest replicated transactions.

Syntax
DBCC OPENTRAN [( [ 'database' | database_id | 0 ] ) ] [WITH TABLERESULTS] [, [NO_INFOMSGS] ] ]

Key:
TABLERESULTS - Output in a tabular format that can be loaded into a table.
NO_INFOMSGS - Suppress all information messages (severity 0-10)Example

BEGIN TRAN...Insert/Update/DeleteGODBCC OPENTRAN;ROLLBACK TRAN;“If love does not know how to give and take without restrictions, it is not love, but a transaction that never fails to lay stress on a plus and a minus” - Emma Goldman

Equivalent Oracle command:

Select from V$TRANSACTION


15. DBCC INPUTBUFFER / OUTPUTBUFFER

Display the last statement sent from a client to a database instance.

Syntax
DBCC INPUTBUFFER (session_id [, request_id ]) [WITH NO_INFOMSGS ]

DBCC OUTPUTBUFFER (session_id [, request_id ]) [WITH NO_INFOMSGS ]

Key:
session_id - The session ID
request_id - A specific request (batch) within the session.
NO_INFOMSGS - Suppress all information messages (severity 0-10)To find the request_ids for a given session id (@@spid = current session):

SELECT request_id
FROM sys.dm_exec_requests
WHERE session_id = @@spid

Example

-- Display session # 52DBCC INPUTBUFFER (52);
DBCC OUTPUTBUFFER (52); "A lawyer who represents himself has a fool for a client." - proverb

Equivalent Oracle command:

Select from v$SESSION



16. DBCC PROCCACHE
Display information about the procedure cache (in table format)

Syntax
DBCC PROCCACHE [WITH NO_INFOMSGS]

Key:
NO_INFOMSGS - Suppress all information messages (severity 0-10)All output figures are Totals:

proc cache size
Entries in the procedure cache.

proc cache used
Entries that are currently being used.

num proc buffs
Pages used by all entries in the procedure cache.

num proc buffs used
Pages used by all entries that are currently being used.

proc cache active / num proc buffs active
For backward compatibility only.

“Misrepresentation and deception are standard operating procedure for this administration, which - to an extent never before seen in U.S. history - systematically and brazenly distorts the facts.” - Paul Krugman (NY Times)

Equivalent Oracle command:

Select from V$SYSSTAT



17. DBCC SHOW_STATISTICS

Display the current distribution statistics for the specified target on the specified table.

Syntax
DBCC SHOW_STATISTICS ('table' | 'view' , target)
[WITH [NO_INFOMSGS] option [, option...] ]

Options:
STAT_HEADER
DENSITY_VECTOR
HISTOGRAM

Key:
table / view - The table or indexed view for which to display stats
target - The column, index or statistics for which to display stats.
NO_INFOMSGS - Suppress all information messages (severity 0-10)
Specifying any of the 3 options will return only those statistics.

Example

DBCC SHOW_STATISTICS ('MySchema.MyTable', MyIndex);GO"A lawyer who represents himself has a fool for a client." - proverb

Related commands:

UPDATE STATISTICS
sp_createstats
sp_updatestats

Equivalent Oracle commands:

INDEX_STATS
DBA_PART_COL_STATISTICS
DBA_TAB_COL_STATISTICS



18. DBCC SHRINKDATABASE
Shrink the size of the database data and log files.

Syntax
DBCC SHRINKDATABASE ('database' option [,option] ) [WITH NO_INFOMSGS]

DBCC SHRINKDATABASE ('database_id' option [,option] ) [WITH NO_INFOMSGS]

DBCC SHRINKDATABASE (0 option [,option]) [WITH NO_INFOMSGS]

Options:
target_percent
NOTRUNCATE
TRUNCATEONLY

Key:
0 - Shrink the current database

target_percent - Percentage of free space to remain in the database file

NOTRUNCATE - Free space at the end of the data file is not returned to the OS
(pages are still moved)
TRUNCATEONLY - Release free space at the end of the data file to the OS
(do not move pages)
NO_INFOMSGS - Suppress all information messages (severity 0-10)
Only one of the truncate options can be specified - they do not apply to log files.

Example

DBCC SHRINKDATABASE (MyDatabase);“Men shrink less from offending one who inspires love than one who inspires fear” - Niccolo Machiavelli

Related commands:

ALTER DATABASE
DBCC SHRINKFILE
FILE_ID
sys.database_files

Equivalent Oracle command:

ALTER DATABASE... DATAFILE...


19. DBCC SHRINKFILE
Shrink the size of the current database data / log file or empty a file by moving the data.

Syntax
DBCC SHRINKFILE ( file , EMPTYFILE ) [WITH NO_INFOMSGS]

DBCC SHRINKFILE ( file , target_size
[, {NOTRUNCATE | TRUNCATEONLY }] ) [WITH NO_INFOMSGS ]

Key:
file - Logical file name or file_id

EMPTYFILE - Migrate data to other files in the same filegroup.
The file can be removed with ALTER DATABASE.

target_size - The size for the file in megabytes.
default = that specified when the file was created, or
the last size used with ALTER DATABASE.(int)

NOTRUNCATE - Free space at the end of the data file is not returned to the OS
(pages are still moved)
TRUNCATEONLY - Release free space at the end of the data file to the OS
(do not move pages)
NO_INFOMSGS - Suppress all information messages (severity 0-10)
You can shrink a transaction log file while the system is in use (DML commands are also being executed), however this will only affect the inactive portion of the transaction log file.
Discover the file_ID for each file with the SQL: SELECT file_id, name FROM sys.database_files;
After using TRUNCATE_ONLY you must perform a full backup

Examples

Shrink a datafile to 64 Mb:

DBCC SHRINKFILE (MyDataFile01, 64);Shrink a Log file to 8 GiB (8192 MiB):

USE MyDatabase;
GO
DBCC SHRINKFILE(MyDatabase_Log, 8192)BACKUP LOG MyDatabase WITH TRUNCATE_ONLYDBCC SHRINKFILE(MyDatabase_Log, 8192)
Afterwords, perform a full backup of the database.
To make the file as small as possible you can specify 1 for 1 Mb, or just leave out the target_size completely, be aware that doing this will slow down the system a little as the system will just have to grow the log file again as soon as another transaction is started.

In SQL Server 2008 the procedure is slightly different, the database must first be set to Simple recovery mode, then shrink the file, then restore FULL recovery mode:

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
go
DBCC SHRINKFILE(MyDatabase_log)
go
EXEC sp_helpdb MyDatabase
go
ALTER DATABASE MyDatabase SET RECOVERY FULL
go “Men shrink less from offending one who inspires love than one who inspires fear” - Niccolo Machiavelli

Related:

Why you want to be restrictive with shrink of database files - karaszi.com
ALTER DATABASE MyDatabase SET RECOVERY FULL;
DBCC SHRINKDATABASE
FILE_ID
sys.database_files
Equivalent Oracle command: ALTER DATABASE Datafile '/oradata/ss64.dbf' resize 64M;



20. DBCC SQLPERF
Display transaction-log space statistics. Reset wait and latch statistics.

Syntax
DBCC SQLPERF ( Option ) [WITH NO_INFOMSGS ]

Options:
LOGSPACE 'sys.dm_os_latch_stats' , CLEAR 'sys.dm_os_wait_stats' , CLEAR

Key:
LOGSPACE - Monitor log space, indicates when to back up or truncate the tx log.
dm_os_latch_stats - Reset the latch statistics.
dm_os_wait_stats - Reset the wait statistics.
NO_INFOMSGS - Suppress all information messages (severity 0-10)
Example

DBCC SQLPERF(LOGSPACE)GO“If you happen to be one of the fretful minority... never force an idea; you'll abort it if you do. Be patient and you'll give birth to it when the time is ripe. Learn to wait” - Robert A. Heinlein

Related commands:

sp_spaceused
sys.dm_os_latch_stats
sys.dm_os_wait_stats

Equivalent Oracle commands:

Oracle deals with transaction logs in a *totally* different way
See V$LOG and V$LOGFILE


21. DBCC TRACEON - Enable trace flags.
DBCC TRACEOFF - Disable trace flags.
DBCC TRACESTATUS - Display the status of trace flags.

Syntax
DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [WITH NO_INFOMSGS]

DBCC TRACEOFF ( trace# [ ,...n ] [ , -1 ] ) [WITH NO_INFOMSGS]

DBCC TRACESTATUS ( [ [trace# [,...n ] ] [,] [-1] ] ) [WITH NO_INFOMSGS]

Key:

trace# - Number of the trace flag(s)
-1 - Display the status of trace flags that are enabled globally.
NO_INFOMSGS - Suppress all information messages.
By default all trace flags that are enabled for the session are displayed.

Examples

-- Turn flag on
DBCC TRACEON (3205)GO
-- Turn flag on globally
DBCC TRACEON (2528, -1)GO
-- Turn flag off
DBCC TRACEOFF (3205);GO
-- Show flag status
DBCC TRACESTATUS (2528, 3205)GO“Every branch of human knowledge, if traced up to its source and final principles vanishes into a mystery” - Arthur Machen

Related commands:

Trace Flags

Equivalent Oracle command:

Server Parameters Configuration settings



22. DBCC UPDATEUSAGE
Report and correct page and row count inaccuracies in catalog views, use this after upgrading a database to SQL Server 2005.

Syntax
DBCC UPDATEUSAGE ( database
[, {table | view} [,{index} ] ] )
[WITH [ NO_INFOMSGS ] [ , ] [COUNT_ROWS ] ]

Key:
database - 'database_name' or database_id or 0 (current db)
NO_INFOMSGS - Suppress all information messages.
COUNT_ROWS - Update the row count column.
The table/view and indexes may be specified by 'name' (in single quotes) or ID.

If 0 is specified, the current database will be used.

Example

DBCC UPDATEUSAGE ('MyDatabase','MySchema.MyTable');GO“No matter how cynical you get, it is impossible to keep up” - Lily Tomlin

Related commands:

sp_spaceused
sys.sysindexes
UPDATE STATISTICS

Equivalent Oracle command:

DBA_TABLES - Rows in table
DBA_TAB_COL_STATISTICS - Other column stats



23. DBCC USEROPTIONS
Return the SET options currently active (set for the current connection.)

Syntax
DBCC USEROPTIONS [WITH NO_INFOMSGS]

Options:

NO_INFOMSGS - Suppress all information messages.
Example

DBCC USEROPTIONS“Eventually, all things merge into one, and a river runs through it. The river was cut by the world's great flood and runs over rocks from the basement of time” - Norman Maclean, A River Runs Through It

Related commands:

SET
SET TRANSACTION ISOLATION LEVEL

Equivalent Oracle command:

DBA_TABLES - Rows in table
DBA_TAB_COL_STATISTICS - Other column stats



24. DBCC - Deprecated commands
DBCC concurrencyviolation (reset | display | startlog | stoplog)

DBCC dbreindex - - use ALTER INDEX instead

DBCC DBREPAIR - - use DROP DATABASE instead

DBCC INDEXDEFRAG

DBCC PINTABLE

DBCC SHOWCONTIG

DBCC UNPINTABLE



25. DBCC - Undocumented commands
These commands may affect system performance and/or force table-level locks.
There is no guarantee these commands will remain available in any future release of SQL server.

DBCC activecursors [(spid)]

DBCC addextendedproc (function_name, dll_name)

DBCC addinstance (objectname, instancename)

DBCC adduserobject (name)

DBCC auditevent (eventclass, eventsubclass, success, loginname, rolename, dbusername, loginid)

DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])

DBCC balancefactor (variance_percent)

DBCC bufcount [(number_of_buffers)]

DBCC buffer ( {'dbname' | dbid} [, objid [, number [, printopt={0|1|2} ] [, dirty | io | kept | rlock | ioerr | hashed ]]])

DBCC bytes ( startaddress, length )

DBCC cachestats

DBCC callfulltext

DBCC checkdbts (dbid, newTimestamp)]

DBCC checkprimaryfile ( {'FileName'} [, opt={0|1|2|3} ])

DBCC cacheprofile [( {actionid} [, bucketid])

DBCC clearspacecaches ('database_name'|database_id, 'table_name'|table_id, 'index_name'|index_id)

DBCC collectstats (on | off)

DBCC config

DBCC cursorstats ([spid [,'clear']])

DBCC dbinfo [('dbname')]

DBCC dbrecover (dbname [, IgnoreErrors])

DBCC dbreindexall (db_name/db_id, type_bitmap)

DBCC dbrepair ('dbname', DROPDB [, NOINIT])

DBCC dbtable [({'dbname' | dbid})]

DBCC debugbreak

DBCC deleteinstance (objectname, instancename)

DBCC des [( {'dbname' | dbid} [, {'objname' | objid} ])]

DBCC detachdb [( 'dbname' )]

DBCC dropextendedproc (function_name)

DBCC dropuserobject ('object_name')

DBCC dumptrigger ({'BREAK', {0 | 1}} | 'DISPLAY' | {'SET', exception_number} | {'CLEAR', exception_number})

DBCC errorlog

DBCC extentinfo [({'database_name'| dbid | 0} [,{'table_name' | table_id} [, {'index_name' | index_id | -1}]])]

DBCC fileheader [( {'dbname' | dbid} [, fileid])

DBCC fixallocation [({'ADD' | 'REMOVE'}, {'PAGE' | 'SINGLEPAGE' | 'EXTENT' | 'MIXEDEXTENT'} , filenum, pagenum [, objectid, indid])

DBCC flush ('data' | 'log', dbid)

DBCC flushprocindb (database)

DBCC freeze_io (db)

DBCC getvalue (name)

DBCC icecapquery ('dbname', stored_proc_name [, #_times_to_icecap (-1 infinite, 0 turns off)])
Use 'dbcc icecapquery (printlist)' to see list of SP's to profile.
Use 'dbcc icecapquery (icecapall)' to profile all SP's.

DBCC incrementinstance (objectname, countername, instancename, value)

DBCC ind ( { 'dbname' | dbid }, { 'objname' | objid }, { indid | 0 | -1 | -2 } )

DBCC invalidate_textptr (textptr)

DBCC invalidate_textptr_objid (objid)

DBCC iotrace ( { 'dbname' | dbid | 0 | -1 } , { fileid | 0 }, bufsize, [ { numIOs | -1 } [, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )

DBCC latch ( address [, 'owners'] [, 'stackdumps'])

DBCC lock ([{'DUMPTABLE' | 'DUMPSTATS' | 'RESETSTATS' | 'HASH'}] | [{'STALLREPORTTHESHOLD', stallthreshold}])

DBCC lockobjectschema ('object_name')

DBCC log ([dbid[,{0|1|2|3|4}[,['lsn','[0x]x:y:z']|['numrecs',num]|['xdesid','x:y'] | ['extent','x:y']|['pageid','x:y']|['objid',{x,'y'}]|['logrecs', {'lop'|op}...]|['output',x,['filename','x']]...]]])

DBCC loginfo [({'database_name' | dbid})]

DBCC matview ({'PERSIST' | 'ENDPERSIST' | 'FREE' | 'USE' | 'ENDUSE'})

DBCC memobjlist [(memory object)]

DBCC memorymap

DBCC memorystatus

DBCC memospy

DBCC memusage ([IDS | NAMES], [Number of rows to output])

DBCC monitorevents ('sink' [, 'filter-expression'])

DBCC newalloc - please use checkalloc instead

DBCC no_textptr (table_id , max_inline)

DBCC page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ][, cache={0|1} ])

DBCC perflog

DBCC perfmon

DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2} , targetfile, targetpg, order={1|0})

DBCC procbuf [({'dbname' | dbid}[, {'objname' | objid} [, nbufs[, printopt = { 0 | 1 } ]]] )]

DBCC prtipage (dbid, objid, indexid [, [{{level, 0} | {filenum, pagenum}}] [,printopt]])

DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]

DBCC readpage ({ dbid, 'dbname' }, fileid, pageid , formatstr [, printopt = { 0 | 1} ])

DBCC rebuild_log (dbname [, filename])

DBCC renamecolumn (object_name, old_name, new_name)

DBCC resource

DBCC row_lock (dbid, tableid, set) - Not Needed

DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)

DBCC ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)

DBCC setcpuweight (weight)

DBCC setinstance (objectname, countername, instancename, value)

DBCC setioweight (weight)

DBCC showdbaffinity

DBCC showfilestats [(file_num)]

DBCC showoffrules

DBCC showonrules

DBCC showtableaffinity (table)

DBCC showtext ('dbname', {textpointer | {fileid, pageid, slotid[,option]}})

DBCC showweights

DBCC sqlmgrstats

DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, 'THREADID'}] )]

DBCC tab ( dbid, objid )

DBCC tape_control {'query' | 'release'}[,('\\.\tape')]

DBCC tec [( uid[, spid[, ecid]] )]

DBCC textall [({'database_name'|database_id}[, 'FULL' | FAST] )]

DBCC textalloc ({'table_name'|table_id}[, 'FULL' | FAST])

DBCC thaw_io (db)

DBCC upgradedb (db)

DBCC usagegovernor (command, value)

DBCC useplan [(number_of_plan)]

DBCC wakeup (spid)

DBCC writepage ({ dbid, 'dbname' }, fileid, pageid, offset, length, data)



26. DBCC PAGE: Use this command to look at contents of a data page stored in SQL Server.

Example:

DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])

where:

Dbid or dbname: Enter either the dbid or the name of the database in question.

Pagenum: Enter the page number of the SQL Server page that is to be examined.

Print option: (Optional) Print option can be either 0, 1, or 2. 0 - (Default) This option causes DBCC PAGE to print out only the page header information. 1 - This option causes DBCC PAGE to print out the page header information, each row of information from the page, and the page's offset table. Each of the rows printed out will be separated from each other. 2 - This option is the same as option 1, except it prints the page rows as a single block of information rather than separating the individual rows. The offset and header will also be displayed.

Cache: (Optional) This parameter allows either a 1 or a 0 to be entered. 0 - This option causes DBCC PAGE to retrieve the page number from disk rather than checking to see if it is in cache. 1 - (Default) This option takes the page from cache if it is in cache rather than getting it from disk only.

Logical: (Optional) This parameter is for use if the page number that is to be retrieved is a virtual page rather then a logical page. It can be either 0 or 1. 0 - If the page is to be a virtual page number. 1 - (Default) If the page is the logical page number.



27. DBCC SHOWCONTIG: Used to show how fragmented data and indexes are in a specified table. If data pages storing data or index information becomes fragmented, it takes more disk I/O to find and move the data to the SQL Server cache buffer, hurting performance. This command tells you how fragmented these data pages are. If you find that fragmentation is a problem, you can reindex the tables to eliminate the fragmentation. Note: this fragmentation is fragmentation of data pages within the SQL Server MDB file, not of the physical file itself.

Since this command requires you to know the ID of both the table and index being analyzed, you may want to run the following script so you don't have to manually look up the table name ID number and the index ID number.

Example:

DBCC SHOWCONTIG (Table_id, IndexID)

Or:

--Script to identify table fragmentation

--Declare variables
DECLARE
@ID int,
@IndexID int,
@IndexName varchar(128)

--Set the table and index to be examined
SELECT @IndexName = 'index_name' --enter name of index
SET @ID = OBJECT_ID('table_name') --enter name of table

--Get the Index Values
SELECT @IndexID = IndID
FROM sysindexes
WHERE id = @ID AND name = @IndexName

--Display the fragmentation
DBCC SHOWCONTIG (@id, @IndexID)

While the DBCC SHOWCONTIG command provides several measurements, the key one is Scan Density. This figure should be as close to 100% as possible. If the scan density is less than 75%, then you may want to reindex the tables in your database.

you can search this link also ss64.com/sql/