DBCC

Question 1

DBCC INPUTBUFFER

Answer 1

Displays the last statement sent from a client to an instance of Microsoft SQL Server.

Question 2

DBCC OPENTRAN

Answer 2

Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database.

Question 3

DBCC OUTPUTBUFFER

Answer 3

Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.

Question 4

DBCC PROCCACHE

Answer 4

Displays information in a table format about the procedure cache.

Question 5

DBCC SHOW_STATISTICS

Answer 5

DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view.

Question 6

DBCC SHOWCONTIG

Answer 6

Displays fragmentation information for the data and indexes of the specified table or view.

Deprecated: Use sys.dm_db_index_physical_stats

Question 7

DBCC SQLPERF

Answer 7

Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.

Question 8

DBCC TRACESTATUS

Answer 8

Displays the status of trace flags.

Question 9

DBCC USEROPTIONS

Answer 9

Returns the SET options active (set) for the current connection.

Question 10

DBCC CHECKALLOC

Answer 10

Checks the consistency of disk space allocation structures for a specified database.

Question 11

DBCC CHECKCATALOG

Answer 11

Checks for catalog consistency within the specified database. The database must be online.

Question 12

DBCC CHECKCONSTRAINTS

Answer 12

Checks the integrity of a specified constraint or all constraints on a specified table in the current database.

Question 13

DBCC CHECKDB

Answer 13

Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

• Runs DBCC CHECKALLOC on the database.
• Runs DBCC CHECKTABLE on every table and view in the database.
• Runs DBCC CHECKCATALOG on the database.
• Validates the contents of every indexed view in the database.
• Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
• Validates the Service Broker data in the database.

Question 14

DBCC CHECKFILEGROUP

Answer 14

Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.

Question 15

DBCC CHECKIDENT

Answer 15

Checks the current identity value for the specified table in SQL Server and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

Question 16

DBCC CHECKTABLE

Answer 16

Checks the integrity of all the pages and structures that make up the table or indexed view.

Question 17

DBCC CLEANTABLE

Answer 17

Reclaims space from dropped variable-length columns in tables or indexed views.

Question 18

DBCC DBREINDEX

Answer 18

Rebuilds one or more indexes for a table in the specified database.

Deprecated: Use ALTER INDEX

Question 19

DBCC DROPCLEANBUFFERS

Answer 19

Removes all clean buffers from the buffer pool.

Question 20

DBCC FREEPROCCACHE

Answer 20

Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

Question 21

DBCC INDEXDEFRAG

Answer 21

Defragments indexes of the specified table or view.

Deprecated: Use ALTER INDEX

Question 22

DBCC SHRINKDATABASE

Answer 22

Shrinks the size of the data and log files in the specified database.

Question 23

DBCC SHRINKFILE

Answer 23

Shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.

Question 24

DBCC UPDATEUSAGE

Answer 24

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

Question 25

DBCC dllname (FREE)

Answer 25

Unloads the specified extended stored procedure DLL from memory.

Question 26

DBCC FREESESSIONCACHE

Answer 26

Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

Question 27

DBCC FREESYSTEMCACHE

Answer 27

Releases all unused cache entries from all caches. The SQL Server Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries. However, you can use this command to manually remove unused entries from all caches or from a specified Resource Governor pool cache.

Question 28

DBCC HELP

Answer 28

Returns syntax information for the specified DBCC command.

Question 29

DBCC TRACEOFF

Answer 29

Disables the specified trace flags.

Question 30

DBCC TRACEON

Answer 30

Enables the specified trace flags.

Question 31

DBCC MEMORYSTATUS

Answer 31

The DBCC MEMORYSTATUS command provides a snapshot of the current memory status.