Useful SQL Server DBCC Commands

DBCC TRACEON & DBCC TRACEOFF: Used to turn on and off trace flags. Trace flags are often used to turn on and off specific server behavior or server characteristics temporarily. In rare occasions, they can be useful to troubleshooting SQL Server performance problems.

Example:

To use the DBCC TRACEON command to turn on a specified trace flag, use this syntax:

DBCC TRACEON (trace# [,...n])

To use the DBCC TRACEON command to turn off a specified trace flag, use this syntax:

DBCC TRACEOFF (trace# [,...n])

You can also use the DBCC TRACESTATUS command to find out which trace flags are currently turned on in your server using this syntax:

DBCC TRACESTATUS (trace# [,...n])

For specific information on the different kinds of trace flags available, search this website or look them up in Books Online. [6.5, 7.0, 2000] Updated 3-20-2006

*****

DBCC UPDATEUSAGE: The official use for this command is to report and correct inaccuracies in the sysindexes table, which may result in incorrect space usage reports. Apparently, it can also fix the problem of unreclaimed data pages in SQL Server. You may want to consider running this command periodically to clean up potential problems. This command can take some time to run, and you want to run it during off times because it will negatively affect SQL Server's performance when running. When you run this command, you must specify the name of the database that you want affected.

Example:

DBCC UPDATEUSAGE ('databasename')

[7.0, 2000] Updated 3-20-2006