Some Undocumented Informational SQL Server 2014 DBCC Commands
In this article, you can find the description of some useful undocumented informational DBCC
commands, and find out how you can use these commands in SQL Server 2014 to gather and display
various types of information.
The command DBCC TRACEON (3604) is issued before each of the following DBCC examples in order
to better demonstrate the effects of the command by displaying a trace of the output of the
DBCC command. It is not actually required to run the DBCC TRACEON (3604) command. If you run
any of the DBCC commands below without the DBCC TRACEON (3604), the command runs, but you
do not see what it is doing.
1. DBCC BUFFER
This command can be used to display buffer headers and pages from the buffer cache.
Syntax:
DBCC BUFFER ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])
where
dbid|dbname – database id|database name
objid|objname – object id|object name
nbufs – number of buffers to display
printopt – print option
0 – print out only the buffer header and page header
1 – print out each row separately and the offset table
2 – print out each row as a whole and the offset table
This example displays buffer headers and pages for the spt_monitor table in the master database:
DBCC TRACEON (3604)
DBCC BUFFER (master, ‘dbo.spt_monitor’)
2. DBCC CONFIG
This command shows the server’s level DS_CONFIG information. The DBCC CONFIG returns the same
DS_CONFIG information as the DBCC RESOURCE returns, but the DBCC RESOURCE returns also RESOURCE
and PERFMON information.
Syntax:
DBCC CONFIG
Example:
DBCC TRACEON (3604)
DBCC CONFIG
3. DBCC RESOURCE
This command shows the server’s level RESOURCE, PERFMON and DS_CONFIG information.
Syntax:
DBCC RESOURCE
Example:
DBCC TRACEON (3604)
DBCC RESOURCE
4. DBCC DBINFO
This command can be used to display DBINFO structure for the specified database.
Syntax:
DBCC DBINFO [(dbname)]
where
dbname – is the database name to display the structure.
This example displays the DBINFO structure of the master database:
DBCC TRACEON (3604)
DBCC DBINFO (master)
5. DBCC DBTABLE
This command displays the contents of the DBTABLE structure.
Syntax:
DBCC DBTABLE ({dbid|dbname})
where
dbid|dbname – database name or database ID to display the structure.
This example displays the DBTABLE structure of the master database:
DBCC TRACEON (3604)
DBCC DBTABLE (master)
6. DBCC IND
This command shows all pages in use by indexes of the specified table.
Syntax:
DBCC IND (dbid|dbname, objid|objname, printopt = {-2|-1|0|1|2|3})
where
dbid|dbname – database ID or database name
objid|objname – object ID or object name
printopt – print option
Example:
DBCC TRACEON (3604)
DBCC IND (master, ‘dbo.spt_monitor’, 0)
7. DBCC LOG
This command can be used to view the transaction log for the specified database.
Syntax:
DBCC LOG ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )
where
dbid or dbname – either the dbid or the name of the database to view the transactional log.
type – is the type of output:
0 – minimum information (operation, context, transaction id)
1 – more information (plus flags, tags, row length, description)
2 – very detailed information (plus allocation unit name, number of locks, page id, slot id, etc)
3 – full information about each operation
4 – returns as option 1 plus hexadecimal dump of the current transaction log’s row.
-1 – returns as option 3 plus hexadecimal dump of the current transaction log’s row.
by default type = 0
To view the transaction log for the master database, run the following command:
DBCC LOG (master)
8. DBCC LOGINFO
This command returns one row per virtual log file for the database. If you do not specify the
database name or database ID, the current database will be used.
Syntax:
DBCC LOGINFO [({dbid|dbname})]
where
dbid|dbname – is a database ID or database name to get the VLF (virtual log files) information.
Remarks:
When SQL Server 2014 shrinks the transaction log file it move log records from the end of the log
file toward the beginning of the log file. A transaction log file is shrunk in units of virtual
log files. When the Status column for the VLF is 0, then the VLF is not in use, when the Status
column for the VLF is 2, then the VLF is in use.
This example returns virtual log files for the master database:
DBCC LOGINFO (master)
9. DBCC MEMORYSTATUS
This command returns the current memory status of SQL Server 2014. The DBCC MEMORYSTATUS command
is a diagnostic tool that used by Microsoft Product Support Services to troubleshoot issues that
relate to the memory consumption of SQL Server or to specific out-of-memory errors.
Syntax:
DBCC MEMORYSTATUS
Example:
DBCC MEMORYSTATUS
10. DBCC PAGE
This command can be used to view the data page structure.
Syntax:
DBCC PAGE ({dbid|dbname}, filenum, pagenum [, printopt={0|1|2|3} ])
where
dbid|dbname – either the database ID or database name
filenum – file id in the specified database
pagenum – page number to display the structure
printopt – either 0 (the default), 1, 2 or 3
To display the page header information for the page number 47 in the master database, run the
following command:
DBCC TRACEON (3604)
DBCC PAGE (master, 1, 47)