SQL Server 7 - DBCC
Terms
undefined, object
copy deck
- DBCC CHECKDB
- checks allocation and structural integrity of all objects in the specified db
- DBCC CHECKCATALOG
- checks for consistency in and between system tables in a db
-
DBCC PINTABLE
DBCC UNPINTABLE -
1. marks a table to be pinned, meaning SQL Server does not flush pages from memory.
2. opposite of 1 - DBCC SHRINKFILE
-
shrinks the size of a db or log file
({file name | file_id}{[, target_size] | [,{EMPTYFILE|NOTRUNCATE|TRUNCATEONLY}]) - DBCC OUTPUTBUFFER(spid)
- returns the current output buffer in hexidecimal and ASCII format for specified spid
- DBCC INPUTBUFFER(spid)
- displays last statment sent to SQL Server from spid
- DBCC SQLPERF(LOGSPACE)
- stats about the use of transaction log space in all databases
- DBCC OPENTRAN
- info about the oldest acitve transacton (normal, distributed, replication etc)
- DBCC [dll name](FREE)
- unloads a specified extended stored procedure from memory. must be sysadmin to run.
- DBCC PROCCACHE
- displays info about the procedure cache
-
DBCC TRACEOFF
DBCC TRACEON
DBCC TRACESTATUS -
1. disables specified trace flags(s)
2. enbables trace flages
3. status of trace flags - DBCC DBREINDEX
- rebuilds one or more indexes for a table-- can also do all
- DBCC CHECKTABLE
- checks integrity of data, index, text, ntext image in table. same repair options as DBCC CHECKDB
- repair_fast
-
for DBCC CHECKDB
makes minor repairs such as extra keys in non clustered indexes - repair_rebuild
-
for DBCC CHECKDB
does everything repair_fast doest plus more time consuming repairs like rebuilding indexes - repair_allow_data_loss
- repair_rebuild plus fixes allocation errors, structural row and page errors and deletion of corrupt text objects. can be handled in a trans
- DBCC UPDATEUSEAGE
- reports and corrects inaccuracies in the sysindexes table which may result in incorrect space usage reports by sp_spaceused
- DBCC CHECKIDENT
-
checks current identity value and corrects it if needed
options:
RECEED, NORECEED - DBCC CHECKFILEGROUP
- checks allocation and structural integrity of all tables in a filegroup
- DBCC CHECKALLOC
-
checks allocation and use of pages in db specified.
3 repair options: REPAIR_fAST, REPAIR_REBUILD, REPAIR_ALLOW_DATA_LOSS - DBCC SHOW_STATISTICS
-
displays distribution statistics for target on table
(table, target) - DBCC USEROPTIONS
- returns set options that are active(set) for the current connection
- DBCC SHOWCONTIG
- fragmentation information for the data and indexes on a table
- DBCC SHRINKDATABASE
- decrease the size of the data files. using the NOTRUNCATE option reorders the data to the front of the file but doesn't release free space to the os
- DBCC SHOWCONTIG (3 outputs)
-
scans table or index and reports on how densly packed the data is. outputs:
1. scan density
2. logical scan fragmentation
3. extent scan fragmentation