DBCC Commands

Using Database Consistency Checker (DBCC) commands can give you valuable insight into what's going on inside your SQL Server system. DBCC commands have powerful documented functions and many undocumented capabilities that you might not know about. You can learn more about undocumented DBCC commands by using DBCC HELP with the 2520 trace flag turned on. Here are seven undocumented DBCC commands that can give you a peek at what's going on inside your SQL Server system. To use these commands, first set the trace flag to 3604 by using the DBCC TRACEON (3604) command.

7. DBCC DBTABLE

The DBCC DBTABLE command displays the system information for the specified database's tables. The command takes a single parameter that specifies the database name.

DBCC DBTABLE(pubs)

6. DBCC DBINFO

The DBCC DBINFO command returns the database information structure for the specified database. The command takes one parameter, which identifies the database.

DBCC DBINFO(pubs)

>

5. DBCC PROCBUF

The DBCC PROCBUF command displays the contents of the SQL Server Procedure Buffer, a storage area where SQL Server caches executable statements such as stored procedures and SQL queries. This command shows procedure and buffer-header information. The parameters specify the database, the stored procedure, the buffer number, and the print option, in that order.

DBCC PROCBUF('master', 'sp_help',1, 0)

4. DBCC BUFFER

The DBCC BUFFER command displays the buffer headers and pages from SQL Server's buffer cache, where SQL Server stores results. The parameters specify the database name and the object name.

DBCC BUFFER(pubs, 'sysobjects')

3. DBCC SHOWFILESTATS

The DBCC SHOWFILESTATS statement returns space usage information about all the data files in the active database. The command returns one record for each data file used in the database.

DBCC SHOWFILESTATS

2. DBCC PAGE

The DBCC PAGE command shows the data page structure that a given database uses. The first parameter specifies the database name, the second parameter specifies the page number, and the third parameter is the print option.

DBCC PAGE('pubs', 1, 1)

1. DBCC LOG

The DBCC LOG command shows the transaction log for the specified database. The DBCC LOG command's first parameter is the database name, and the second parameter specifies the level of information to show to the user.

DBCC LOG('pubs', 3)