Understanding DBCC (Database Console Commands) is a step toward becoming a better and well-rounded database administrator (DBA) for Workforce Central. DBCC is a command in Transact-SQL that helps you implement maintenance, validation, and information commands against SQL Server. DBCC cannot be executed by itself: it always needs to have a second part to specify the command. Think of it as an extensible catchall for command functionality in SQL Server.

A common misperception is that DBCC stands for "database consistency checker." Checking the consistency of the database is one of the primary uses for DBCC, but not the only one. You really can't understand DBCC until you pair it with a command. In fact, DBCC is just semantic "sugar" for the Transact-SQL parser. Your take away: don't pigeon hole DBCC into one type of functionality. 

DBCC SQLPERF

The DBCC SQLPERF command provides transaction log space usage statistics for all databases. This is helpful for understanding log growth and size. You execute this command in SQL Server Management Studio as follows:

DBCC SQLPERF(LOGSPACE)

DBCC SQLPERF returns these columns:

  • Database Name: As the name implies, this is the database name.
  • Log Size (MB): This is the current size that is allocated to the log.
  • Log Space Used (%): This is the percentage of the log file that is currently occupied by transaction log information.
  • Status: This is always zero, which makes the column unimportant.

clip_image002

If your log files are configured to grow automatically, when the percentage of log space used approaches 100 percent, the log size is increased. When your transaction logs are backed up, the percentage of log space used is close to zero. For more information about transaction log backups, see the Working with Transaction Log Backups topic in SQL Server Books Online.

From DBCC SQLPERF, you can determine these things:

  • If your transaction log is close to being full
  • The size of your transaction log on the disk

DBCC HELP

The DBCC HELP command returns all the commands that you can implement with DBCC and the details about each of the commands. To see all the commands, execute the following:

DBCC HELP ('?') WITH NO_INFOMSGS

In SQL Server Management Studio, the commands are returned on the Messages tab, and they look like this:

clip_image004

If you need the syntax for a specific command, such as CHECKALLOC, you can execute DBCC HELP and pass the command name:

DBCC HELP('checkalloc')

This command returns the following in SQL Server 2005:

clip_image006

 

Summary

DBCC commands provide enormous benefits to the well-informed DBA. I will be writing a series of posts with information about the additional DBCC commands that you should know about.

Wayne Berry (6230289B-5BEE-409e-932A-2F01FA407A92) is a computer programmer and serial entrepreneur with a passion for dynamic Web sites that run on SQL Server. When he is not blogging for MSDN, you can find him blogging for Project 31-A. http://www.31a2ba2a-b718-11dc-8314-0800200c9a66.com