SQL Server 7 - DBCC Flash Cards

Play Memory | Create Card File | Append to Card File
Title: SQL Server 7 - DBCC
Description: DBCC commands for SQL Server 7
Number of Cards: 26
Save Count: 1
Author: support21
Created: 2001-02-22
Tags: microsoft
Private No

Save Count represents the number of people who have saved this card set to their flashcard list. Consider this an endorsement!

    • Question
    • Answer
    • Side 3
    • 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 current stats for an index or collection on a table
      (table, target)

      indicates selectivity
    • 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 SHOW_STATISTICS
    • displays distribution statistics for target on table
      (table, target)