DBCC commands can be really useful while debugging your SQL Server. Below I’ve listed 8 commands you’ll want to keep in your pocket.

DBCC DROPCLEANBUFFERS

DBCC DROPCLEANBUFFERS

Amazingly does what is says on the tin. Dumps all pages from memory. Great for performance testing. Try not to do this on a production system unless you enjoy answering support calls from annoyed users.

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE (N'MyDatabase', 10 /* = Percent To Leave Free */);

Also does what is says on the tin. This will shrink all files in the target database (plus the log) and release unused space from files back to the operation system. This works by blindly moving pages from the end of the file up the free spaces in the beginning of the file, without any consideration for consolidation of your data. Great if you enjoy increasing fragmentation like a maniac, or you let that development database grow a wee bit too much.

If you do have to run it, consider DBCC SHRINKFILE instead and do it one file at a time, so you don’t have to come back the next day. It also might be a good idea to run a full re-index too on your database, to get those pages back in shape again.

DBCC SHRINKFILE

DBCC SHRINKFILE ('SomeFile', 10 /* = Target File Size In MB */)

Same as DBCC SHRINKDATABASE but for a specific file. Same warnings apply. Best to use this one file at a time. But then again, best to pre-allocate your database files to begin with, if you’re able to.

DBCC FREEPROCCACHE

DBCC FREEPROCCACHE

Dumps all the cached query plans. Useful for progressive load testing when paired with DBCC DROPCLEANBUFFERS. Also useful if SQL Server has gone bonkers and cached a really nasty plan for some query. If this does happen, check if your statistics are updated, as the Query Optimizer usually knows best - at least with the information it has.

DBCC CHECKDB

DBCC CHECKDB /* runs on the current database */
DBCC CHECKDB(MyDatabase) /* runs on the target database */
DBCC CHECKDB(MyDatabase, REPAIR_REBUILD) /* tries to repair the database, if only recoverable corruption has occurred */
DBCC CHECKDB(MyDatabase, REPAIR_ALLOW_DATA_LOSS) /* tries to repair the database, even if data loss occurs */

Verifies database consistency and corruption. Errors get returned in nice red messages where you can find “etc etc etc Page (1:789) etc etc etc”. The 1 refers to the number of the file containing the fault. The 789 is the faulty page number in the referred file. Recovering from this feels like theoretical physics from what I’ve seen out there, and seems to range from letting CHECKDB run its course, to selectively retrieve rows from specific backups.

DBCC SQLPERF

Shows what your log file usage looks like. Useful to see if some database is getting ready to blow up due to lack of log space, especially if you have fixed log file size.

DBCC SQLPERF(LOGSPACE)

DBCC INPUTBUFFER

Now this will work magic. If you give it a SPID, it will show you the last query ran by that SPID. Great quick and dirty way to figure out what query is blocking some other process.

DBCC INPUTBUFFER()

To lookup a list of running processes, you can, for example, run:

SELECT * FROM sys.sysprocesses

DBCC HELP

For a refresher on what DBCC stuff you can do, DBCC HELP will provide information on the usage of specific DBCC commands, plus a list of available commands to start with… that have help available, at least.

For a list of stuff just run:

DBCC HELP ('?')

To get help on say, INPUTBUFFER, just run:

DBCC HELP ('INPUTBUFFER')