Mustafa EL-Masry

Concept and basics of DBCC Commands in SQL Server

March 31, 2017 by

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

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

Basic Commands:

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

DBCC HELP:

  • Provides syntax for a specific DBCC command, or lists all commands.
  • 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:

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


Figure 1 DBCC HELP command

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


Figure 2 DBCC HELP (CHECKDB) command

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.

DBCC TRACEON, DBCC TRACEOFF, and 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:

  • DBCC TRACEON and DBCC TRACEOFF can be used to enable/disable a trace flag without requiring a service restart.
  • With DBCC TRACEON and DBCC TRACEOFF, you can enable/disable trace flags at either the session or the global level.
  • 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 TRACESTATUS provides status for a specific trace flag, or all of them, and notes whether they are enabled for a session or globally.
  • Running DBCC TRACESTATUS does not affect performance or data, nor does it alter the configuration of the instance.
  • DBCC TRACESTATUS only needs the public role to be run.

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

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

Use this code to list all trace flags enabled globally:

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

Run a full backup operation for one of your databases:

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

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:


Figure 3 Log file viewer (1)

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

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

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

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:


Figure 4 Log file viewer (2)

You can disable this trace flag using the following code:

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:

Informational Commands:

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

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
  • To run this command, you need to have( VIEW SERVER STATE, ALTER SERVER STATE )
  • Execution does not affect system performance

To check log space utilization:


Figure 5 Result of DBCC SQLPERF Command

To clear wait statistics:

To clear latch statistics:

To clear spinlock 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

To implement this DBCC Command go through the following steps:

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

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

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


Figure 6 Result of DBCC SHOW_STATISTICS Command

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

DBCC USEROPTIONS:

  • Returns options set for the current connection like isolation level or 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


Figure 7 Result of DBCC USEROPTIONS Command

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.

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.

See more

Consider these free tools for SQL Server that improve database developer productivity.

References


Mustafa EL-Masry

Mustafa EL-Masry

Mustafa EL-Masry is a Senior database consultant and one of the experts in Database performance tuning in the Middle East.

Currently, he is working as a Senior consultant production DBA and Development DBA in many projects in multiple government sectors. He is a Top SQL Server blogger in the Middle East, founder of the community mostafaelmasry.com, and is the second Arabic author on Microsoft MSDN in SQL Server.

Based on his current position, he solved fairly interesting problems on fairly large databases and highly sensitive performance cases.

View all posts by Mustafa EL-Masry
Mustafa EL-Masry
SQL Commands

About Mustafa EL-Masry

Mustafa EL-Masry is a Senior database consultant and one of the experts in Database performance tuning in the Middle East. Currently, he is working as a Senior consultant production DBA and Development DBA in many projects in multiple government sectors. He is a Top SQL Server blogger in the Middle East, founder of the community mostafaelmasry.com, and is the second Arabic author on Microsoft MSDN in SQL Server. Based on his current position, he solved fairly interesting problems on fairly large databases and highly sensitive performance cases. View all posts by Mustafa EL-Masry

1,803 Views