sql dbcc

Many DBA’s and database developers aren’t very familiar with DBCC commands (aka Database Console Commands) because they don’t always have the chance to work with them. In this article, we’ll provide a simple primer on DBCC commands with specific examples

许多DBA和数据库开发人员对DBCC命令（又名数据库控制台命令）不太熟悉，因为他们并不总是有机会使用它们。 在本文中，我们将提供带有特定示例的DBCC命令的简单入门。

Note: These commands many of them require sysadmin or db_owner permission.

注意：这些命令很多都需要sysadmin或db_owner权限。

基本命令： (Basic Commands:)

These are a good place to begin if you have not worked with DBCC commands previously starting from the upcoming ones:

如果您以前从未使用即将发布的DBCC命令，那么这些是一个不错的起点。

DBCC HELP:

DBCC帮助：

• Provides syntax for a specific DBCC command, or lists all commands.

  提供特定DBCC命令的语法，或列出所有命令。

• By default, only supported commands listed

  默认情况下，仅列出受支持的命令

• This command has no impact on performance or data

  此命令对性能或数据没有影响

• Requires sysadmin role

  需要系统管理员角色

We have two options to run this DBCC Command:

我们有两个选择来运行此DBCC命令：

When you just run it as it is, it will list all of the supported DBCC commands

照原样运行时，它将列出所有受支持的DBCC命令

 
DBCC HELP ('?');

When you run it, providing a specific command, it will give you the syntax for that:

当您运行它时，提供一个特定的命令，它将为您提供语法：

 
DBCC HELP (CHECKDB);
GO

It will be very helpful if you don’t have internet access to get easy the syntax of any DBCC command just execute DBCC Help.

如果您没有Internet访问权限，那么只需执行DBCC帮助，即可轻松获得任何DBCC命令的语法，这将非常有帮助。

DBCC TRACEON, DBCC TRACEOFF, and DBCC TRACESTATUS:

DBCC TRACEON，DBCC TRACEOFF和DBCC TRACESTATUS：

If you have worked with trace flags before, you probably know that you can apply them as a startup parameter for the SQL Server service. Well, that is all well and good until you realize that for them to take effect, you have to restart the service. That is not always something you can easily do so that I can list some of the useful features of these commands:

如果以前使用过跟踪标志，则可能知道可以将它们用作SQL Server服务的启动参数。 好吧，这一切都很好，直到您意识到要使它们生效，您必须重新启动服务。 这并非总是可以轻松实现的，因此我可以列出这些命令的一些有用功能：

• DBCC TRACEON and DBCC TRACEOFF can be used to enable/disable a trace flag without requiring a service restart.

  DBCC TRACEON和DBCC TRACEOFF可用于启用/禁用跟踪标志，而无需重新启动服务。

• With DBCC TRACEON and DBCC TRACEOFF, you can enable/disable trace flags at either the session or the global level.

  使用DBCC TRACEON和DBCC TRACEOFF，可以在会话级别或全局级别启用/禁用跟踪标志。

• Be aware that when you enable or disable a trace flag, it’s quite possible that you can affect performance, depending on what functionality the trace flag changes.

  请注意，启用或禁用跟踪标记时，很有可能会影响性能，具体取决于跟踪标记更改的功能。

• Both commands (DBCC TRACEON and DBCC TRACEOFF) require the sysadmin role.

  这两个命令（DBCC TRACEON和DBCC TRACEOFF）都需要sysadmin角色。

• DBCC TRACESTATUS provides status for a specific trace flag, or all of them, and notes whether they are enabled for a session or globally.

  DBCC TRACESTATUS提供特定跟踪标志或所有跟踪标志的状态，并记录是否为会话或全局启用了它们。

• Running DBCC TRACESTATUS does not affect performance or data, nor does it alter the configuration of the instance.

  运行DBCC TRACESTATUS不会影响性能或数据，也不会更改实例的配置。

• DBCC TRACESTATUS only needs the public role to be run.

  DBCC TRACESTATUS仅需要运行公共角色。

Now, Let us see some examples of using these commands:

现在，让我们看一些使用这些命令的例子：

Use the following code to list all trace flags enabled just for this connection:

使用以下代码列出为此连接启用的所有跟踪标志：

 
DBCC TRACESTATUS (); 
GO 

Use this code to list all trace flags enabled globally:

使用以下代码列出全局启用的所有跟踪标志：

 
DBCC TRACESTATUS (-1); 
GO

Follow the following scenario to test both of DBCC TRACEON and DBCC TRACEOFF

请按照以下方案来测试DBCC TRACEON和DBCC TRACEOFF

Run a full backup operation for one of your databases:

对您的数据库之一运行完整备份操作：

 
DECLARE @BackupPath NVARCHAR (100);
SET @BackupPath = 'C:\Backup\SQL_Shack2014_' + REPLACE (CONVERT (nvarchar (19), SYSDATETIME (), 126), ':','') + '.bak';BACKUP DATABASE [SQL_Shack2014]TO DISK = @BackupPathWITH NOFORMAT, INIT,   COMPRESSION,  STATS = 05;
GO

Then run a transaction log backup operation on the same database:

然后在同一数据库上运行事务日志备份操作：

 
DECLARE @BackupPath NVARCHAR (100);
SET @BackupPath = 'C:\Backup\SQL_Shack2014_' + REPLACE (CONVERT (nvarchar (19), SYSDATETIME (), 126), ':','') + '.trn';BACKUP LOG [SQL_Shack2014]TO DISK = @BackupPathWITH NOFORMAT, INIT,   COMPRESSION,  STATS = 05;
GO

Now, when you check the ERRORLOG, you will notice entries to the SQL Server error log for every successful backup operation (local connection) as in the following figure:

现在，当您检查ERRORLOG时，对于每个成功的备份操作（本地连接），您将注意到SQL Server错误日志中的条目，如下图所示：

We can suppress all successful in SQL Server error log by enabling trace flag 3226 using DBCC TRACEON command:

通过使用DBCC TRACEON命令启用跟踪标志3226，我们可以抑制SQL Server错误日志中的所有成功：

 
DBCC TRACEON (3226);
GO

Then we can use DBCC TRACESTATUS command to verify that the trace is enabled:

然后，我们可以使用DBCC TRACESTATUS命令来验证是否启用了跟踪：

 
DBCC TRACESTATUS (); 
GO 

Run a transaction log Backup operation again and check SQL Server log to see the impact of enabling this trace flag:

再次运行事务日志备份操作，并检查SQL Server日志以查看启用此跟踪标志的影响：

 
DECLARE @BackupPath NVARCHAR(100);
SET @BackupPath = 'C:\Backup\SQL_Shack2014_' + REPLACE (CONVERT (nvarchar (19), SYSDATETIME (), 126), ':','') + '.trn';BACKUP LOG [SQL_Shack2014]TO DISK = @BackupPathWITH NOFORMAT, INIT,   COMPRESSION,  STATS = 05;
GO

You will notice that there is a log record for enabling the trace flag 3226, and the last transaction log backup has not logged as the following figure:

您会注意到，有一个用于启用跟踪标志3226的日志记录，并且上一个事务日志备份尚未记录，如下图所示：

You can disable this trace flag using the following code:

您可以使用以下代码禁用此跟踪标志：

 
DBCC TRACEOFF (3226);
GO

To make these commands affect on SQL server instance globally which will be effective till the next restart of the instance, you can use the following codes:

为了使这些命令在全局范围内影响到SQL Server实例（在实例的下一次重新启动之前一直有效），可以使用以下代码：

 
/*Enable trace flag (3226) globally
*/
DBCC TRACEON (3226, -1);
GO
/*Verify again that the trace flag is enabled globally
*/	
DBCC TRACESTATUS (-1); 
GO 
/*Turn off 3226 globally
*/
DBCC TRACEOFF (3226,-1);
GO

信息命令： (Informational Commands:)

These commands will not change the configuration of our environment, but they will give us information about our environment.

这些命令不会更改环境的配置，但是会向我们提供有关环境的信息。

DBCC SQLPERF:

DBCC SQLPERF：

• ProvidesTransaction log space usage for all log files on an instance

  提供实例上所有日志文件的事务日志空间使用情况

• Used to clear out data related to weight statistics, latch statistics, or spinlock statistics

  用于清除与重量统计，闩锁统计或自旋锁统计有关的数据

• VIEW SERVER STATE, ALTER SERVER STATE ) VIEW SERVER STATE，ALTER SERVER STATE ）
• Execution does not affect system performance

  执行不影响系统性能

To check log space utilization:

要检查日志空间利用率：

 
DBCC SQLPERF (LOGSPACE);

To clear wait statistics:

清除等待统计信息：

 
DBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR);

To clear latch statistics:

要清除闩锁统计信息：

 
DBCC SQLPERF ("sys.dm_os_latch_stats", CLEAR);

To clear spinlock statistics:

清除自旋锁统计信息：

 
DBCC SQLPERF ("sys.dm_os_spinlock_stats", CLEAR);

DBCC SHOW_STATISTICS:

DBCC SHOW_STATISTICS：

• Displays statistics object for an index, indexed view, or column statistic

  显示索引，索引视图或列统计信息的统计信息对象

• Use when troubleshooting and understanding the estimates from a plan and comparing against actual values

  在进行故障排除和了解计划的估算并与实际值进行比较时使用

• Viewing statistics does not introduce a performance load on the system

  查看统计信息不会对系统造成性能负担

• Require sysadmin, db_owner, or db_ddladmin roles, or tablet ownership

  需要sysadmin，db_owner或db_ddladmin角色或平板电脑所有权

To implement this DBCC Command go through the following steps:

要实现此DBCC命令，请执行以下步骤：

First, you need to view all indexes contained in the specified table:

首先，您需要查看指定表中包含的所有索引：

 
USE [SQL_Shack2014];
GO
sp_helpindex '[dbo]. [Students]';
GO

Then you can use DBCC SHOW_STATISTICS against the index name you got in step 1:

然后，您可以对在步骤1中获得的索引名称使用DBCC SHOW_STATISTICS：

 
DBCC SHOW_STATISTICS ('[dbo]. [Students]','NonClusteredIndex-20170307-095338');
GO

The result displays the header, histogram, and density vector based on data stored in the statistics object.

结果基于存储在统计对象中的数据显示标题，直方图和密度矢量。

And you can specify what section of the result you want to see by using one of the following options:

您可以使用以下选项之一指定要查看结果的哪一部分：

 
DBCC SHOW_STATISTICS ('[dbo]. [Students]','NonClusteredIndex-20170307-095338') WITH STAT_HEADER;
GODBCC SHOW_STATISTICS ('[dbo]. [Students]','NonClusteredIndex-20170307-095338') WITH DENSITY_VECTOR;
GODBCC SHOW_STATISTICS ('[dbo]. [Students]','NonClusteredIndex-20170307-095338') WITH HISTOGRAM;
GO

DBCC USEROPTIONS:

DBCC用户：

• Returns options set for the current connection like isolation level or QUOTED_IDENTIFIER

  返回为当前连接设置的选项，例如隔离级别或QUOTED_IDENTIFIER

• Use to verify setting for connection to confirm they are correct, or consistent across different connect methods

  用于验证连接设置以确认它们正确或在不同连接方法中一致

• Only displays information, does not modify settings

  仅显示信息，不修改设置

• Require public role

  需要公开角色

 
DBCC USEROPTIONS;
GO

摘要 (Summary)

As DBA or Database Developer, you can see the benefits DBCC Commands can offer for you. You may need one or more of them, which can provide you with a simpler way to implement your needs.

作为DBA或数据库开发人员，您可以看到DBCC命令可以为您提供的好处。 您可能需要其中一个或多个，这可以为您提供一种更简单的方式来实现您的需求。

In this article, I have covered only top Basic DBCC commands. Hopefully I can cover the more advanced commands in another article. I hope this article has been informative for you.

在本文中，我仅介绍了主要的基本DBCC命令。 希望我可以在另一篇文章中介绍更高级的命令。 希望本文对您有所帮助。

翻译自: https://www.sqlshack.com/concept-and-basics-of-dbcc-commands-in-sql-server/

sql dbcc