Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 7: Some Useful Undocumented DBCC Commands

By Alexander Chigrik,



Introduction

In this article I want to tell you about some useful undocumented DBCC commands, and how you can use these commands in SQL Server 7.0 for administering and monitoring.

DBCC is an abbreviation of a DataBase Consistency Checker. This is the description of DBCC from SQL Server Books Online:

A statement used to check the logical and physical consistency of a database, check memory usage, decrease the size of a database, check performance statistics, and so on. Database consistency checker (DBCC) ensures the physical and logical consistency of a database, but is not corrective.

Undocumented DBCC commands

1. DBCC BUFFER

This command can be used to print 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 examine
printopt
print option
0 - print out only the buffer header and page header (default)
1 - print out each row separately and the offset table
2 - print out each row as a whole and the offset table

This is the example:

DBCC TRACEON (3604)
dbcc buffer(pubs,'sysobjects')

2. DBCC BYTES

This command can be used to dump out bytes from a specific address. Syntax:

dbcc bytes ( startaddress, length )

where

startaddress
starting address to dump
length
number of bytes to dump

This is the example:

DBCC TRACEON (3604)
dbcc bytes (1000000, 100)

3. DBCC DBINFO

Print DBINFO structure for specified database.

DBCC DBINFO [( dbname )]

where

dbname
is the database name.

This is the example:

DBCC TRACEON (3604)
DBCC DBINFO (pubs)

4. DBCC DBTABLE

This command prints out the contents of the DBTABLE structure. Syntax:

DBCC DBTABLE ({dbid|dbname})

where

dbid|dbname
database name or database ID

This is the example:

DBCC TRACEON (3604)
DBCC DBTABLE (pubs)

The DBTABLE structure has an output parameter called dbt_open. This parameter keeps track of how many users are in the database.

Look at here for more details: FIX: Database Usage Count Does Not Return to Zero

5. DBCC DES

Prints the contents of the specified DES (descriptor). Syntax:

dbcc des [( [dbid|dbname] [,objid|objname] )]

where

dbid|dbname
database id|database name.
objid|objname
object id|object name

This is the example:

DBCC TRACEON (3604)
DBCC DES

6. DBCC HELP

DBCC HELP returns syntax information for the specified DBCC statement. In comparison with DBCC HELP command in version 6.5, it returns syntax information only for the documented DBCC commands.

Syntax:

DBCC HELP ('dbcc_statement' | @dbcc_statement_var | '?')

This is the example:

DBCC TRACEON (3604)
DECLARE @dbcc_stmt sysname
SELECT @dbcc_stmt = 'CHECKTABLE'
DBCC HELP (@dbcc_stmt)

Look at here for more details: DBCC HELP (T-SQL)

7. DBCC IND

Shows all pages in use by indexes of the specified table. Syntax:

dbcc ind( dbid|dbname, objid|objname, [printopt = { 0 | 1 | 2 }] )

where

dbid|dbname
database id|database name.
objid|objname
object id|object name
printopt
print option

This is the example:

DBCC TRACEON (3604)
DBCC IND (pubs, authors)

8. DBCC log

This command is used to view the transactional log for the specified database.

DBCC log ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )

where

Dbid or dbname
Enter either the dbid or the name of the database in question.
type
is the type of output:
0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length)
2 - very detailed information (plus object name, index name, page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump of the current transaction log's row.
-1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XACTID
by default type = 0

To view the transaction log for the master database, you can run the following command:

DBCC log (master)

9. DBCC PAGE

You can use this command to view the data page structure.

DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])

where

Dbid or dbname
Enter either the dbid or the name of the database in question.
Pagenum
Enter the page number of the SQL Server page that is to be examined.
Print option
(Optional) Print option can be either 0, 1, or 2.
0 - (Default) This option causes DBCC PAGE to print out only the page header information.
1 - This option causes DBCC PAGE to print out the page header information, each row of information from the page, and the page's offset table. Each of the rows printed out will be separated from each other.
2 - This option is the same as option 1, except it prints the page rows as a single block of information rather than separating the individual rows. The offset and header will also be displayed.
Cache
(Optional) This parameter allows either a 1 or a 0 to be entered.
0 - This option causes DBCC PAGE to retrieve the page number from disk rather than checking to see if it is in cache.
1 - (Default) This option takes the page from cache if it is in cache rather than getting it from disk only.
Logical
(Optional) This parameter is for use if the page number that is to be retrieved is a virtual page rather then a logical page. It can be either 0 or 1.
0 - If the page is to be a virtual page number.
1 - (Default) If the page is the logical page number.

In this example one data page is viewed from the table titleauthor, database pubs.

USE pubs
GO
DBCC TRACEON (3604)
GO
DECLARE @pgid int
SELECT @pgid = first FROM sysindexes WHERE
  id = object_id('titleauthor') AND indid = 1
DBCC PAGE (pubs, @pgid, 1)
GO

This is the result from my computer:

...

DATA:
Offset 32 -
011e9820:  04042000 3137322d 33322d31 31373650  .. .172-32-1176P
011e9830:  53333333 33016400 0000051a 16150f04  S3333.d.........
...

Look at here for more details: Data page structure in MS SQL 6.5

10. DBCC procbuf

This command prints procedure buffer headers and proc-headers from the procedure cache.

Syntax:

DBCC procbuf( [dbid], [objid], [nbufs], [printopt = {0|1}] )

where

dbid
database ID
objid
object ID
nbufs
number of buffers to print
printopt
print option
(0 print out only the proc buff and proc header (default)
1 print out proc buff, proc header and contents of buffer)

This is the example:

DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('master')
SELECT @objectid = object_id('sp_help')
DBCC procbuf(@dbid,@objectid,1,0)

11. DBCC prtipage

This command prints the page number pointed to by each row on the specified index page.

Syntax:

DBCC prtipage( dbid, objid, indexid, indexpage )

where

dbid
database ID
objid
object ID
indexid
index ID
indexpage
the logical page number of the index page to dump

This is the example:

DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('pubs')
SELECT @objectid = object_id('authors')
DBCC prtipage(@dbid,@objectid,1,0)

12. DBCC pss

This command shows info about processes currently connected to the dataserver. Structure is the source of data contained in the sysprocesses table.

Syntax:

DBCC pss( suid, spid, printopt = { 1 | 0 } )

where

suid
server user ID
spid
server process ID
printopt
print option
(0 standard output,
1 all open DES's and current sequence tree)

This is the example:

DBCC TRACEON (3604)
dbcc pss

13. DBCC resource

This command shows dataserver level RESOURCE, PERFMON and DS_CONFIG info. RESOURCE shows addresses of various data structures used by the server. PERFMON structure contains master..spt_monitor field info. DS_CONFIG structure contains master..syscurconfigs field info.

Syntax:

DBCC resource

This is the example:

DBCC TRACEON (3604)
DBCC resource

14. DBCC TAB

You can use the following undocumented command to view the data pages structure (in comparison with DBCC PAGE, this command will return information about all data pages for viewed table, not only for particular number)

Syntax:

DBCC tab (dbid, objid)

where

dbid
is the database id
objid
is the table id

This is the example:

DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('pubs')
SELECT @objectid = object_id('authors')
DBCC TAB (@dbid,@objectid)

Literature

  1. "What are all the dbcc commands for SQL Server?" NTFAQ http://www.ntfaq.com/Articles/Index.cfm?ArticleID=14186
  2. INFO: Description of DBCC PAGE Command http://support.microsoft.com/support/kb/articles/Q83/0/65.ASP
  3. THE UNAUTHORIZED DOCUMENTATION OF DBCC http://user.icx.net/~huntley/dbccinfo.htm
  4. The Totally Unauthorized List of Sybase DBCC Commands http://www.kaleidatech.com/dbcc1.htm
  5. The Totally Unauthorized List of Sybase DBCC Commands http://www.kaleidatech.com/dbcc2.htm
  6. The Totally Unauthorized List of Sybase DBCC Commands http://www.kaleidatech.com/dbcc3.htm
  7. FIX: Database Usage Count Does Not Return to Zero http://support.microsoft.com/support/kb/articles/Q175/3/03.ASP
  8. DBCC HELP (T-SQL) http://msdn.microsoft.com/library/psdk/sql/dbcc_10.htm
Total article views: 6915 | Views in the last 30 days: 4
 
Related Articles
FORUM

database diagram print on one page

database diagram print

FORUM

Database Options

Database Options

FORUM

DBCC Shrinkfile - EmptyFile option

Long duration running shrinkfile with EmptyFile option command

FORUM

Please give me the solution for Print option in SSRS

Print and export options in SSRS

FORUM

Database Option

Checking for database option in Mgt Studio

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones