Start Studying!
Add Cards ↓
Deck Info
Description
Tags
Recent Users
Other Decks By This User
- 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
- 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
