I need help with DBCC

2

June 6, 2016 by Kenneth Fisher

Many years ago during an interview I was asked to “Name the top 7 DBCC commands that you use.” I think I was able to name 3. Ever since then I’ve paid particular attention to DBCC commands in preparation of the day when I hear that question again. Well not to long ago I was watching Erin Stellato’s (b/t) PluralSight course “SQL Server: Understanding and Using DBCC Commands”. Note: It’s a great course and I highly recommend it. In it she goes over quite a few DBCC commands, some documented, some not. In noted two in particular.

The first one I noted gives you a list of the size and free space in the logs of all databases on the instance. Highly useful. I’ve used it repeatedly recently. Unfortunately my memory is not always the best and I can never quite remember the exact name. Which brings us to the other command.

DBCC help

There are two important uses of this command. First we can use it to list out all of the other DBCC commands.

DBCC HELP ('?') WITH NO_INFOMSGS

On it’s own it will list out all documented DBCC commands. However you can also add in trace flag 2588 to get the undocumented ones as well.

DBCC TRACEON(2588) WITH NO_INFOMSGS;
DBCC HELP ('?') WITH NO_INFOMSGS;

Note: Prior to 2005 it was trace flag 2520.

I can now skim down the list to find the command I want. My memory may not be good enough to pull it out of thin air but with a list I can find it very quickly.

DBCC sqlperf

Now DBCC sqlperf does two things. It can be used to clear certain types of wait statistics, or give back space information on the logs. I’m not 100% certain how it works so on to the second use of DBCC help!

DBCC HELP ('sqlperf') WITH NO_INFOMSGS

dbcc sqlperf ( LOGSPACE ) [ WITH NO_INFOMSGS ]

You’ll note that this doesn’t give us a whole lot of information about the command. Just the command options themselves. This makes it great for a quick reminder, but it certainly doesn’t replace BOL.

2 thoughts on “I need help with DBCC

  1. […] Now that the indexes are created we need to tell SQL to use them. To do this we use the undocumented DBCC command AUTOPILOT. Even though it is undocumented we can still get information on it using DBCC HELP. […]

  2. […] case you hadn’t noticed, I love built in help. DBCC HELP, the script button in SSMS, even BOL. Well here’s another […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,628 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: