1.
DBCC CHECKALLOC
DBCC CHECKALLOC checks page usage and allocation in the database. Use this command if allocation errors are found for the database. If you run DBCC CHECKDB, you do not need to run DBCC CHECKALLOC, as DBCC CHECKDB includes the same checks (and more) that DBCC CHECKALLOC performs.
2. DBCC CHECKCATALOG
This command checks for consistency in and between system tables. This command is not executed within the DBCC CHECKDB command, so running this command weekly is recommended.
3. DBCC CHECKCONSTRAINTS
DBCC CHECKCONSTRAINTS alerts you to any CHECK or constraint violations.
Use it if you suspect that there are rows in your tables that do not meet the constraint or CHECK constraint rules.
4. DBCC CHECKDB
A very important DBCC command, DBCC CHECKDB should run on your SQL Server instance on at least a weekly basis. Although each release of SQL Server reduces occurrences of integrity or allocation errors, they still do happen. DBCC CHECKDB includes the same checks as DBCC CHECKALLOC and DBCC CHECKTABLE. DBCC CHECKDB can be rough on concurrency, so be sure to run it at off-peak times.
5. DBCC CHECKTABLE
DBCC CHECKTABLE is almost identical to DBCC CHECKDB, except that it is performed at the table level, not the database level. DBCC CHECKTABLE verifies index and data page links, index sort order, page pointers, index pointers, data page integrity, and page offsets. DBCC CHECKTABLE uses schema locks by default, but can use the TABLOCK option to acquire a shared table lock. CHECKTABLE also performs object checking using parallelism by default (if on a multi-CPU system).
6. DBCC CHECKFILEGROUP
DBCC CHECKFILEGROUP works just like DBCC CHECKDB, only DBCC CHECKFILEGROUP checks the specified filegroup for allocation and structural issues. If you have a very large database (this term is relative, and higher end systems may be more apt at performing well with multi-GB or TB systems ) , running DBCC CHECKDB may be time-prohibitive.
If your database is divided into user defined filegroups, DBCC CHECKFILEGROUP will allow you to isolate your integrity checks, as well as stagger them over time.
7. DBCC CHECKIDENT
DBCC CHECKIDENT returns the current identity value for the specified table, and allows you to correct the identity value if necessary.
8. DBCC DBREINDEX
If your database allows modifications and has indexes, you should rebuild your indexes on a regular basis. The frequency of your index rebuilds depends on the level of database activity, and how quickly your database and indexes become fragmented. DBCC DBREINDEX allows you to rebuild one or all indexes for a table. Like DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, running DBREINDEX during peak activity times can significantly reduce concurrency.
9. DBCC INDEXDEFRAG
Microsoft introduced the excellent DBCC INDEXDEFRAG statement beginning with SQL Server 2000. This DBCC command, unlike DBCC DBREINDEX, does not hold long term locks on indexes. Use DBCC INDEXDEFRAG for indexes that are not very fragmented, otherwise the time this operation takes will be far longer then running DBCC DBREINDEX. In spite of it's ability to run during peak periods, DBCC INDEXDEFRAG has had limited effectiveness compared to DBCC DBREINDEX (or drop/create index).
10. DBCC INPUTBUFFER
The DBCC INPUTBUFFER command is used to view the last statement sent by the client connection to SQL Server. When calling this DBCC command, you designate the SPID to examine. (SPID is the process ID, which you can get from viewing current activity in Enterprise Manager or executing sp_who. )
11. DBCC OPENTRAN
DBCC OPENTRAN is a Transact-SQL command that is used to view the oldest running transaction for the selected database. The DBCC command is very useful for troubleshooting orphaned connections (connections still open on the database but disconnected from the application or client), and identification of transactions missing a COMMIT or ROLLBACK. This command also returns the oldest distributed and undistributed replicated transactions, if any exist within the database. If there are no active transactions, no data will be returned. If you are having issues with your transaction log not truncating inactive portions, DBCC OPENTRAN can show if an open transaction may be causing it.
12. DBCC PROCCACHE
You may not use this too frequently, however it is an interesting DBCC command to execute periodically, particularly when you suspect you have memory issues. DBCC PROCCACHE provides information about the size and usage of the SQL Server procedure cache.
13. DBCC SHOWCONTIG
The DBCC SHOWCONTIG command reveals the level of fragmentation for a specific table and its indices. This DBCC command is critical to determining if your table or index has internal or external fragmentation. Internal fragmentation concerns how full an 8K page is.
When a page is underutilized, more I/O operations may be necessary to fulfill a query request than if the page was full, or almost full.
External fragmentation concerns how contiguous the extents are. There are eight 8K pages per extent, making each extent 64K. Several extents can make up the data of a table or index. If the extents are not physically close to each other, and are not in order, performance could diminish.
14. DBCC SHRINKDATABASE
DBCC SHRINKDATABASE shrinks the data and log files in your database.
Avoid executing this command during busy periods in production, as it has a negative impact on I/O and user concurrency. Also remember that you cannot shrink a database past the target percentage specified, shrink smaller than the model database, shrink a file past the original file creation size, or shrink a file size used in an ALTER DATABASE statement.
15. DBCC SHRINKFILE
DBCC SHRINKFILE allows you to shrink the size of individual data and log files. (Use sp_helpfile to gather database file ids and sizes).
16. DBCC TRACEOFF, TRACEON, TRACESTATUS
Trace flags are used within SQL Server to temporarily enable or disable specific SQL Server instance characteristics. Traces are enabled using the DBCC TRACEON command, and disabled using DBCC TRACEOFF. DBCC TRACESTATUS is used to displays the status of trace flags. You'll most often see TRACEON used in conjunction with deadlock logging (providing more verbose error information).
17. DBCC USEROPTIONS
Execute DBCC USEROPTIONS to see what user options are in effect for your specific user connection. This can be helpful if you are trying to determine if you current user options are inconsistent with the database options.
DBCC CHECKALLOC checks page usage and allocation in the database. Use this command if allocation errors are found for the database. If you run DBCC CHECKDB, you do not need to run DBCC CHECKALLOC, as DBCC CHECKDB includes the same checks (and more) that DBCC CHECKALLOC performs.
2. DBCC CHECKCATALOG
This command checks for consistency in and between system tables. This command is not executed within the DBCC CHECKDB command, so running this command weekly is recommended.
3. DBCC CHECKCONSTRAINTS
DBCC CHECKCONSTRAINTS alerts you to any CHECK or constraint violations.
Use it if you suspect that there are rows in your tables that do not meet the constraint or CHECK constraint rules.
4. DBCC CHECKDB
A very important DBCC command, DBCC CHECKDB should run on your SQL Server instance on at least a weekly basis. Although each release of SQL Server reduces occurrences of integrity or allocation errors, they still do happen. DBCC CHECKDB includes the same checks as DBCC CHECKALLOC and DBCC CHECKTABLE. DBCC CHECKDB can be rough on concurrency, so be sure to run it at off-peak times.
5. DBCC CHECKTABLE
DBCC CHECKTABLE is almost identical to DBCC CHECKDB, except that it is performed at the table level, not the database level. DBCC CHECKTABLE verifies index and data page links, index sort order, page pointers, index pointers, data page integrity, and page offsets. DBCC CHECKTABLE uses schema locks by default, but can use the TABLOCK option to acquire a shared table lock. CHECKTABLE also performs object checking using parallelism by default (if on a multi-CPU system).
6. DBCC CHECKFILEGROUP
DBCC CHECKFILEGROUP works just like DBCC CHECKDB, only DBCC CHECKFILEGROUP checks the specified filegroup for allocation and structural issues. If you have a very large database (this term is relative, and higher end systems may be more apt at performing well with multi-GB or TB systems ) , running DBCC CHECKDB may be time-prohibitive.
If your database is divided into user defined filegroups, DBCC CHECKFILEGROUP will allow you to isolate your integrity checks, as well as stagger them over time.
7. DBCC CHECKIDENT
DBCC CHECKIDENT returns the current identity value for the specified table, and allows you to correct the identity value if necessary.
8. DBCC DBREINDEX
If your database allows modifications and has indexes, you should rebuild your indexes on a regular basis. The frequency of your index rebuilds depends on the level of database activity, and how quickly your database and indexes become fragmented. DBCC DBREINDEX allows you to rebuild one or all indexes for a table. Like DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, running DBREINDEX during peak activity times can significantly reduce concurrency.
9. DBCC INDEXDEFRAG
Microsoft introduced the excellent DBCC INDEXDEFRAG statement beginning with SQL Server 2000. This DBCC command, unlike DBCC DBREINDEX, does not hold long term locks on indexes. Use DBCC INDEXDEFRAG for indexes that are not very fragmented, otherwise the time this operation takes will be far longer then running DBCC DBREINDEX. In spite of it's ability to run during peak periods, DBCC INDEXDEFRAG has had limited effectiveness compared to DBCC DBREINDEX (or drop/create index).
10. DBCC INPUTBUFFER
The DBCC INPUTBUFFER command is used to view the last statement sent by the client connection to SQL Server. When calling this DBCC command, you designate the SPID to examine. (SPID is the process ID, which you can get from viewing current activity in Enterprise Manager or executing sp_who. )
11. DBCC OPENTRAN
DBCC OPENTRAN is a Transact-SQL command that is used to view the oldest running transaction for the selected database. The DBCC command is very useful for troubleshooting orphaned connections (connections still open on the database but disconnected from the application or client), and identification of transactions missing a COMMIT or ROLLBACK. This command also returns the oldest distributed and undistributed replicated transactions, if any exist within the database. If there are no active transactions, no data will be returned. If you are having issues with your transaction log not truncating inactive portions, DBCC OPENTRAN can show if an open transaction may be causing it.
12. DBCC PROCCACHE
You may not use this too frequently, however it is an interesting DBCC command to execute periodically, particularly when you suspect you have memory issues. DBCC PROCCACHE provides information about the size and usage of the SQL Server procedure cache.
13. DBCC SHOWCONTIG
The DBCC SHOWCONTIG command reveals the level of fragmentation for a specific table and its indices. This DBCC command is critical to determining if your table or index has internal or external fragmentation. Internal fragmentation concerns how full an 8K page is.
When a page is underutilized, more I/O operations may be necessary to fulfill a query request than if the page was full, or almost full.
External fragmentation concerns how contiguous the extents are. There are eight 8K pages per extent, making each extent 64K. Several extents can make up the data of a table or index. If the extents are not physically close to each other, and are not in order, performance could diminish.
14. DBCC SHRINKDATABASE
DBCC SHRINKDATABASE shrinks the data and log files in your database.
Avoid executing this command during busy periods in production, as it has a negative impact on I/O and user concurrency. Also remember that you cannot shrink a database past the target percentage specified, shrink smaller than the model database, shrink a file past the original file creation size, or shrink a file size used in an ALTER DATABASE statement.
15. DBCC SHRINKFILE
DBCC SHRINKFILE allows you to shrink the size of individual data and log files. (Use sp_helpfile to gather database file ids and sizes).
16. DBCC TRACEOFF, TRACEON, TRACESTATUS
Trace flags are used within SQL Server to temporarily enable or disable specific SQL Server instance characteristics. Traces are enabled using the DBCC TRACEON command, and disabled using DBCC TRACEOFF. DBCC TRACESTATUS is used to displays the status of trace flags. You'll most often see TRACEON used in conjunction with deadlock logging (providing more verbose error information).
17. DBCC USEROPTIONS
Execute DBCC USEROPTIONS to see what user options are in effect for your specific user connection. This can be helpful if you are trying to determine if you current user options are inconsistent with the database options.
EXAMPLE FOR EXECUTING THE DBCC:
--. Select and check the
database
--use MyDbName;
DBCC CHECKDB WITH DATA_PURITY ;
--In case if you receive
some error in teh Database tables can you apply for each table the next
sentence, of course first time need to set the database to SINGLE USER MODE.
ALTER DATABASE [CH_DB] SET SINGLE_USER WITH NO_WAIT
--2. Repair the errored
table:
--use MyDbName;
DBCC CHECKTABLE('TABLE_NAME',REPAIR_ALLOW_DATA_LOSS)
--3. After repair do you
need to check again the database, and set to MULTI USER MODE
ALTER DATABASE [CH_DB] SET MULTI_USER WITH NO_WAIT
BASIC AND ADVANCED DBCC:
DBCC TRACEON(2520)
DBCC HELP ('?')
GO
--To learn about syntax of
an individual DBCC command run following script in query analyzer.
DBCC HELP(<command>)
GO
--Following is the list of
all the DBCC commands and their syntax. List contains all documented and
undocumented DBCC commands.
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 checkalloc [('database_name'[,
NOINDEX | REPAIR])]
[WITH NO_INFOMSGS[,
ALL_ERRORMSGS][, ESTIMATEONLY]]
DBCC checkcatalog
[('database_name')]
[WITH NO_INFOMSGS]
DBCC checkconstraints [(
'tab_name' | tab_id | 'constraint_name' | constraint_id )]
[WITH ALL_CONSTRAINTS |
ALL_ERRORMSGS]
DBCC checkdb [('database_name'[,
NOINDEX | REPAIR])]
[WITH NO_INFOMSGS[,
ALL_ERRORMSGS]
[, PHYSICAL_ONLY][,
ESTIMATEONLY][,DBCC TABLOCK]
DBCC checkdbts (dbid, newTimestamp)]
DBCC checkfilegroup [( [
{'filegroup_name' | filegroup_id} ]
[, NOINDEX] )] [WITH NO_INFOMSGS
[, ALL_ERRORMSGS][,
PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]
DBCC checkident
('table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] )
DBCC checkprimaryfile ( {'FileName'} [, opt={0|1|2|3} ])
DBCC checktable ('table_name'[, {NOINDEX | index_id | REPAIR}])
[WITH NO_INFOMSGS[,
ALL_ERRORMSGS]
[, PHYSICAL_ONLY][,
ESTIMATEONLY][, TABLOCK]]
DBCC cleantable
('database_name'|database_id, 'table_name'|table_id,[batch_size])
DBCC cacheprofile [( {actionid}
[, bucketid])
DBCC clearspacecaches ('database_name'|database_id,
'table_name'|table_id, 'index_name'|index_id)
DBCC collectstats (on | off)
DBCC concurrencyviolation (reset | display | startlog | stoplog)
DBCC config
DBCC cursorstats ([spid [,'clear']])
DBCC dbinfo [('dbname')]
DBCC dbrecover (dbname [, IgnoreErrors])
DBCC dbreindex ('table_name' [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]
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 dropcleanbuffers
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 free dll_name (FREE)
DBCC freeproccache
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 indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid |indname})
DBCC inputbuffer (spid)
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 opentran [({'dbname'|
dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]
DBCC outputbuffer (spid)
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 pintable (database_id, table_id)
DBCC procbuf [({'dbname' |
dbid}[, {'objname' | objid}
[, nbufs[, printopt = { 0 |
1 } ]]] )]
DBCC proccache
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 show_statistics ('table_name', 'target_name')
DBCC showcontig (table_id | table_name [, index_id | index_name]
[WITH FAST, ALL_INDEXES,
TABLERESULTS [,ALL_LEVELS]])
DBCC showdbaffinity
DBCC showfilestats
[(file_num)]
DBCC showoffrules
DBCC showonrules
DBCC showtableaffinity (table)
DBCC showtext ('dbname', {textpointer | {fileid, pageid, slotid[,option]}})
DBCC showweights
DBCC shrinkdatabase ({dbid | 'dbname'}, [freespace_percentage
[, {NOTRUNCATE |
TRUNCATEONLY}]])
DBCC shrinkfile ({fileid |
'filename'}, [compress_size
[, {NOTRUNCATE |
TRUNCATEONLY | EMPTYFILE}]])
DBCC sqlmgrstats
DBCC sqlperf
(LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]}
| {THREADS} | {LOGSPACE})
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 traceoff [( tracenum [,
tracenum … ] )]
DBCC traceon [( tracenum [,
tracenum … ] )]
DBCC tracestatus (trace# [, …trace#])
DBCC unpintable (dbid, table_id)
DBCC updateusage ({'database_name'| 0} [, 'table_name' [, index_id]])
[WITH [NO_INFOMSGS] [,] COUNT_ROWS]
DBCC upgradedb (db) DBCC usagegovernor (command, value)
DBCC useplan [(number_of_plan)]
DBCC useroptions DBCC wakeup (spid)
DBCC writepage ({ dbid, 'dbname' }, fileid, pageid, offset, length, data)
