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.