Some Useful Undocumented SQL Server 7.0 and 2000 DBCC Commands

DBCC PAGE 

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

Syntax:

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

where:

dbid|dbname - Enter either the dbid or the name of the database

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.

This is an example:

DBCC TRACEON (3604)
DBCC PAGE (master, 1, 1)

DBCC PROCBUF

This command displays procedure buffer headers and stored procedure headers from the procedure cache.

Syntax:

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

where:

dbid|dbname - database id or the database name

objid|objname - object id or the object name

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 an example:

DBCC TRACEON (3604)
DBCC procbuf(master,'sp_help',1,0)

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 an example:

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

DBCC PSS

This command shows info about processes currently connected to the server.

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 an example:

DBCC TRACEON (3604)
DBCC pss