This is the 17th day of my participation in the August More text Challenge. For details, see: August More Text Challenge

DBCC in SQL Server is very useful, but sometimes not encountered very often. Here’s a little summary.

DBCC is called Database Console Commands, meaning that it is a Database Console command and is often used for troubleshooting purposes.

You must have the sysadmin or db_owner permission to execute DBCC.

Database console command statements can be divided into maintenance, information, validation, miscellaneous and other categories.

DBCC can also stand for Database Consistency Checker, often used in analysis services. Specific introduction can be viewed related materials.

The basicDBCC HELP

The DBCC HELP command is used to view the syntax of a specified DBCC command or list all supported commands

Run the following command to list all supported commands:

DBCC HELP ('? ');
Copy the code

Syntax for listing CHECKDB:

DBCC HELP (CHECKDB);
Copy the code

DBCC HELP (‘? ‘); Only 33 common commands are returned in general. By enabling trace tag 2588, you can view more commands.

DBCC HELP ('? '); More commands can be displayed
DBCC TRACEON(2588);
DBCC HELP ('? ');
Copy the code

DBCC TRACEON, DBCC TRACEOFF, and DBCC TRACESTATUS

DBCC TRACEON and DBCC TRACEOFF are used to enable or disable a trace tag. The DBCC TRACEON and DBCC TRACEOFF can be enabled at the session or global level without restarting the service.

Enabling or disabling trace tags can affect SQL Server performance, depending on what features the tags change.

DBCC TRACESTATUS provides the status of the specified trace tag, or the status of all trace tags that are enabled. Running DBCC TRACESTATUS does not affect performance or data, nor does it change the instance configuration.

DBCC TRACESTATUS only requires public privileges. DBCC TRACEON and DBCC TRACEOFF require the permission of a system administrator.

Check out the section on this is probably the most correct posture for you to learn about SQL Server tracking tags for more details

  • DBCC TRACEON(2588): Indicates that the 2588 flag is enableddbcc helpp('? ');See more DBCC commands.
  • DBCC TRACEON(3604): Enable the 3604 flag to display DBCC Page results on the client, otherwise they will not be displayed

DBCC information command

Let’s take a look at the commands for database environment information

DBCC SQLPERF

DBCC sqlperf:

  • Provides transaction log space usage for all log files in an instance
  • Used to clear data related to weight statistics, latch statistics, or spin lock statistics —Used to clear out data related to weight statistics, latch statistics, or spinlock statistics
  • To run this command, you need to have (VIEW SERVER STATE, ALTER SERVER STATE).
  • The execution does not affect system performance.

Check the log space usage

DBCC SQLPERF (LOGSPACE);
Copy the code

Clear wait statisticsclear wait statistics

DBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR);
Copy the code

Clear latch statisticsclear latch statistics

DBCC SQLPERF ("sys.dm_os_latch_stats", CLEAR);
Copy the code

Example Clear spin-lock statisticsclear spinlock statistics

DBCC SQLPERF ("sys.dm_os_spinlock_stats", CLEAR);
Copy the code

DBCC SHOW_STATISTICS

Displays current query optimization statistics for a table or indexed view.

  • Statistics objects that display index, index view, or column statistics
  • Used in troubleshooting, understanding the estimates in the plan, and comparing them to the actual values
  • Viewing statistics does not impose a performance load on the system
  • The sysadmin, DB_Owner, or DB_DDladmin roles or computer ownership are required

Implement the DBCC command with the following steps

  1. View all the indexes in the table
sp_helpindex '[Person].[Address]';
Copy the code

  1. Used for the index name obtained aboveDBCC SHOW_STATISTICS
DBCC SHOW_STATISTICS ('[Person].[Address]'.'IX_Address_StateProvinceID');
Copy the code

The results show headers, histograms, and density vectors based on the data stored in the statistics object.

  1. You can specify the result section to view using one of the following options
DBCC SHOW_STATISTICS ('[Person].[Address]'.'IX_Address_StateProvinceID') WITH STAT_HEADER;
GO
 
DBCC SHOW_STATISTICS ('[Person].[Address]'.'IX_Address_StateProvinceID') WITH DENSITY_VECTOR;
GO
 
DBCC SHOW_STATISTICS ('[Person].[Address]'.'IX_Address_StateProvinceID') WITH HISTOGRAM;
GO
Copy the code

DBCC USEROPTIONS

  • Returns the options set for the current connection, such as isolation level or QUOTED_IDENTIFIER.
  • Used to verify connection Settings to confirm that they are correct or consistent across different connection methods.
  • Only the information is displayed and the Settings are not modified.
  • Public roles are required.
DBCC USEROPTIONS;
Copy the code

DBCC SHOWCONTIG

DBCC SHOWCONTIG Displays data and index fragmentation information of a specified table or view. The fragments can be found for further treatment.

DBCC INPUTBUFFER

DBCC INPUTBUFFER: displays the last statement sent from the client to the Microsoft SQL Server instance.

DBCC OUTPUTBUFFER

DBCC OUTPUTBUFFER: returns the current OUTPUTBUFFER in hexadecimal and ASCII format with the specified session_id.

DBCC PROCCACHE

DBCC PROCCACHE: Displays information in a table format about the procedure cache. DBCC PROCCACHE: Displays information in a table format about the procedure cache.

DBCC OPENTRAN

DBCC OPENTRAN: Helps identify active transactions that might prevent log truncation.

DBCC OPENTRAN displays transaction logs about a specified database, including information about the earliest active transactions and the earliest distributed and non-distributed replicated transactions, if any. Results are only displayed if there are active transactions in the log or if the database contains replication information. If there are no active transactions in the log, an informational message is displayed.

Non-sql Server publishers do not support DBCC OPENTRAN.

DBCC maintenance commands

The task of maintaining a database, index, or filegroup.

DBCC CLEANTABLE

DBCC CLEANTABLE: Reclaim the space of the deleted variable length columns in a table or indexed view.

DBCC INDEXDEFRAG

DBCC INDEXDEFRAG: Defragments Indexes of the specified table or View DBCC INDEXDEFRAG: Defragments Indexes of the specified table or view DBCC INDEXDEFRAG: Defragments Indexes of the specified table or view DBCC INDEXDEFRAG: Defragments Indexes of the specified table or view

DBCC DBREINDEX

DBCC DBREINDEX: regenerates one or more indexes for tables in a specified database.

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE: Shrinks the data and log file size in the specified database.

DBCC SHRINKFILE

DBCC SHRINKFILE: Shrinks the specified data or log file size of the current database. You can use it to move data from one file to other files in the same file group, which will empty the file and allow the database for that file to be deleted. You can shrink the file to less than the size it was created and reset the minimum file size to the new value.

DBCC DROPCLEANBUFFERS

DBCC DROPCLEANBUFFERS: Deletes all clean buffers from the buffer pool and deletes column store objects from the column store object pool.

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS: WITH NO_INFOMSGS disables all informational messages.

DBCC FREEPROCCACHE

DBCC FREEPROCCACHE: Remove all elements from the schedule cache, remove a specific schedule from the schedule cache by specifying a schedule handle or SQL handle, or remove all cache entries associated with the specified resource pool.

DBCC UPDATEUSAGE

DBCC UPDATEUSAGE: Reports and corrects page and line errors in the catalog view. These inaccuracies may cause the space usage report returned by the SP_SPACEUsed system stored procedure to be incorrect.

DBCC Validation command

An operation that validates the allocation of a database, table, index, directory, file group, or database page.

DBCC CHECKDB

DBCC CHECKDB: Checks the logical and physical integrity of all objects in a specified database by doing the following:

  • Run it on the databaseDBCC CHECKALLOC.
  • Run against every table and view in the databaseDBCC CHECKTABLE.
  • Run it on the databaseDBCC CHECKCATALOG.
  • Verify the contents of each indexed view in the database.
  • When you use FILESTREAM to store varbinary(Max) data ina file system, verify link-level consistency between table metadata, file system directories, and files.
  • Validate the Service Broker data in the database.

This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB.

Databases that include memory-optimized tables support DBCC CHECKDB, but validation is only for disk-based tables. However, the checksum of the files in the memory-optimized file group is verified.

For more information, see the official introduction

DBCC CHECKCONSTRAINTS

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

DBCC CHECKTABLE

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

DBCC CHECKFILEGROUP

DBCC CHECKFILEGROUP: Checks the allocation and structural integrity of all tables and indexed views in the specified file group of the current database.

DBCC CHECKALLOC

DBCC CHECKALLOC: Checks the consistency of the allocation structures of a specified database.

The DBCC CHECKALLOC function is included in the DBCC CHECKDB and DBCC CHECKFILEGROUP. This means that you do not have to run DBCC CHECKALLOC separately from these statements. DBCC CHECKALLOC does not check FILESTREAM data. FILESTREAM stores binary large objects (BLOBS) on the file system.

DBCC CHECKCATALOG

DBCC CHECKCATALOG: Checks catalog consistency in a specified database. The database must be online.

DBCC CHECKIDENT

DBCC CHECKIDENT: Checks the current identity value of a specified table in SQL Server and changes the identity value if necessary. You can also manually set a new current identity value for the identity column using DBCC CHECKIDENT.

DBCC Miscellaneous commands

Miscellaneous tasks, such as enabling trace flags, help commands, or removing DLLS from memory

  • DBCC HELP: returns syntax information about the specified DBCC command
  • DBCC dllName (FREE) : unloads the specified extended stored procedure DLL from memory
  • DBCC DBREPAIR:

Some other DBCC commands are not exposed

There are also DBCC commands for the underlying SYSTEM of SQL Server that are not listed in the official documentation. However, it is very helpful to delve into the database mechanism. The following is a brief list, without verification and analysis. There will be an opportunity to introduce the following articles.

You can click on this section to expand for a brief introduction to these commands.
  • DBCC DBINFO [(‘dbname’)] : returns error information about the database.
  • DBCC BUFFER Displays BUFFER header and page information
  • DBCC FLUSHPROCINDB FLUSHPROCINDB specifies the cached contents of a database stored procedure in the database server’s memory.
  • DBCC DATABLE Displays the table information of the management database
  • DBC IND Displays information about the page used by an index.
  • DBCC REBULDLOG Restores the SQL database transaction log file.
  • DBCC LOG Displays transaction logs of a database
  • DBCC PAGE Displays the data PAGE of a database
  • DBCC PROCBUF displays the buffer headers and stored procedures for the procedure buffer pool.
  • DBCC PRTIPAGE Views the page number pointed to by each row of an index page.
  • DBCC PSS displays information about the processes currently connected to the SQLSERVER server.
  • DBCC RESOURCE Displays the resources currently used by the server.
  • DBCC TAB View the structure of the data page.

And so on.

reference

  • Database Console Commands
  • Concept and basics of DBCC Commands in SQL Server
  • The DBCC command is mandatory for SQLSERVER administrators