|
|
| |
SQL Server 6.5: Some useful undocumented DBCC commands
Alexander Chigrik
chigrik@mssqlcity.com
- Introduction
- Undocumented DBCC commands:
- DBCC allocdump
- DBCC bhash
- DBCC buffer
- DBCC bytes
- DBCC dbinfo
- DBCC dbtable
- DBCC delete_row
- DBCC des
- DBCC extentchain
- DBCC extentcheck
- DBCC extentdump
- DBCC extentzap
- DBCC findnotfullextents
- DBCC help
- DBCC ind
- DBCC locateindexpgs
- DBCC lock
- DBCC log
- DBCC page
- DBCC pglinkage
- DBCC procbuf
- DBCC prtipage
- DBCC pss
- DBCC rebuildextents
- DBCC resource
- DBCC show_bucket
- DBCC tab
- DBCC undo
- Literature
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 6.5
for administering and monitoring.
DBCC is an abbreviation for Database Console Command. DBCC commands are
generally used to check the physical and logical consistency of a database,
although they are also used for a variety of miscellaneous tasks, as you
will see here.
Note, 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 commands
examined below. If you run any of the DBCC commands below without the above
option, the command runs, but you don't see what it is doing.
Undocumented DBCC commands
Here you can find some useful SQL Server 6.5 undocumented DBCC commands.
DBCC allocdump
This command can be used to display all the extents on an allocation page.
Syntax:
DBCC allocdump( dbid, page )
where
dbid - is the database id
page - is the allocation page number
|
Example:
DBCC TRACEON (3604)
DECLARE @dbid int, @pageid int
SELECT @dbid = DB_ID('pubs')
SELECT @pageid = first FROM sysindexes WHERE
id = object_id('titleauthor') AND indid = 1
DBCC allocdump(@dbid, @pageid)
|
DBCC bhash
This command can check the integrity of the buffer hash table, and optionally
print it.
Syntax:
DBCC bhash( { print_bufs | no_print }, bucket_limit )
where
print_bufs - display all buffers (default)
no_print - display only buffers with problems
bucket_limit - number of buffers allowed in a bucket (default = 0)
|
Example:
DBCC TRACEON (3604)
DBCC bhash
|
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 = {0|1|2},buftype)
where
dbid|dbname - database name or database ID
(0 will show data for all databases)
objid|objname - object ID or object name
(0 will show data for all objects)
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
buftype - buffer type to print
|
Example:
DBCC TRACEON (3604)
dbcc buffer(0,'sysobjects')
|
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
|
Example:
DBCC TRACEON (3604)
dbcc bytes (1000000, 100)
|
DBCC DBINFO
Displays DBINFO structure for the specified database.
Syntax:
DBCC DBINFO [( dbname )]
where
dbname - is the database name.
|
Example:
DBCC TRACEON (3604)
DBCC DBINFO (pubs)
|
DBCC DBTABLE
This command displays the contents of the DBTABLE structure.
Syntax:
DBCC DBTABLE ({dbid|dbname})
where
dbid|dbname - database name or database ID
|
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
DBCC delete_row
This command can be used to delete an index or data row, by either
a row number or an offset on a page. DBCC delete_row is a nonlogged
command, so you can delete a particular row without placed it into
the transaction log. Use this command at your own risk!
Syntax:
DBCC delete_row ( dbid|dbname, page, delete_by_row, rownum )
where
dbid|dbname - database ID or database name
page - logical page number
delete_by_row - how delete: by row or by offset
(1 - the next parameter is row number)
(0 - the next parameter is offset on the page)
rownum - row number or offset
|
Example:
SET NOCOUNT ON
GO
USE pubs
GO
DBCC TRACEON (3604)
GO
DECLARE @pgid int
SELECT COUNT(*) FROM titleauthor
SELECT @pgid = first FROM sysindexes WHERE
id = object_id('titleauthor') AND indid = 1
SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
DBCC delete_row (pubs, @pgid, 'row', 1)
SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
SELECT COUNT(*) FROM titleauthor
|
DBCC des
This command used to print the contents of the specified DES (descriptor).
Syntax:
DBCC des( [ dbid|dbname ][, objid ] )
where
dbid|dbname - database ID or database name
objid - object ID
|
Example:
DBCC TRACEON (3604)
DBCC DES
|
DBCC extentchain
This command displays extent header info for all extents in use by
the specified object.
Syntax:
DBCC extentchain(dbid,objid,indexid,sort={1|0},display={1|0} [,order={1|0}])
where
dbid - database ID
objid - object ID
indexid - index ID
sort - report on state of the sort bit
(0 don't include this info)
(1 do report on sort bit status)
display - what to report
(0 display the count only)
(1 display the extents)
order - (optional) order to follow the chain
(0 descending, 1 ascending)
|
Example:
DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('pubs')
SELECT @objectid = object_id('authors')
dbcc extentchain(@dbid,@objectid,0,0,0,0)
|
DBCC extentcheck
This command has the same output as extentchain, and examines all extents
on allocation pages for the specified object.
Syntax:
DBCC extentcheck( dbid, objid, indexid, sort = {1|0} )
where
dbid - database ID
objid - object ID
indexid - index ID
sort - state of the sort bit
(0 don't report this, 1 report sort bit status)
|
Example:
DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('pubs')
SELECT @objectid = object_id('authors')
dbcc extentcheck(@dbid,@objectid,0,1)
|
DBCC extentdump
This command displays an extent dump.
Syntax:
DBCC extentdump( dbid, page )
where
dbid - database ID
page - the number of a page controlled by the extent in question
|
Example:
DBCC TRACEON (3604)
DECLARE @dbid int
SELECT @dbid = DB_ID('pubs')
DBCC extentdump(@dbid, 1)
|
DBCC extentzap
This command can be used to clear all extents matching the parameter values.
Syntax:
DBCC extentzap( dbid, objid, indexid, sort )
where
dbid - database ID
objid - object ID
indexid - index ID
sort - state of the sort bit
|
Example:
DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('pubs')
SELECT @objectid = object_id('authors')
DBCC extentzap(@dbid,@objectid,0,0)
|
DBCC findnotfullextents
This command shows the extent id of extents allocated to the specified
objid that are not full. If the objid is specified, then an indexid can
be specified. If objid is all, then all database tables are displayed.
Syntax:
DBCC findnotfullextents( dbid,objid,indexid, sort = {1|0} )
where
dbid - database ID
objid - object ID
indexid - index ID
sort - state of the sort bit
|
Example:
DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('pubs')
SELECT @objectid = object_id('authors')
DBCC findnotfullextents(@dbid,@objectid,0,0)
|
DBCC HELP
DBCC HELP returns syntax information for the specified DBCC statement.
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)
|
DBCC ind
Shows all pages in use by indexes of the specified table name.
Syntax:
DBCC ind( dbid|dbname, objid, printopt = { 0 | 1 | 2 } )
where
dbid|dbname - database ID or database name
objid - object ID
printopt - print option
(0 print out only the buffer header and page header (default)
1 print page headers, page data in row format, and offset tables
2 print page headers, unformatted page data, and offset tables)
|
Example:
DBCC TRACEON (3604)
declare @obid int
SELECT @obid = object_id('authors')
DBCC ind (pubs, @obid, 1)
|
DBCC locateindexpgs
This command prints all references in the index to the specified page.
Syntax:
DBCC locateindexpgs( dbid, objid, page, indexid, level )
where
dbid - database ID
objid - object ID
page - logical page number of the page for which index
references are being searched
indexid - index ID
level - level within the index to search for references
|
Example:
DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('pubs')
SELECT @objectid = object_id('authors')
DBCC locateindexpgs(@dbid,@objectid,1,1,0)
|
DBCC lock
This command can be used to display lock chains.
Syntax:
DBCC lock
Example:
DBCC TRACEON (3604)
DBCC lock
|
DBCC log
This command is used to view the transaction log for the specified database.
DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )
PARAMETERS:
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.
by default type = 0
|
To view the transaction log for the master database, you can run the
following command:
DBCC log (master)
DBCC PAGE
You can use this command to view the data page structure.
DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])
PARAMETERS:
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 titleauthor table
in the pubs database.
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
DBCC pglinkage
This command can be used to display the page chain, performing
integrity checks during traversal.
Syntax:
DBCC pglinkage(dbid,start,number,printopt={0|1|2},target,order={1|0})
where
dbid - database ID
start - page number with which to start
number - number of pages to examine, or 0 if target is specified
printopt - print option
(0 display only the count of pages scanned
1 display information about the last 16 pages scanned
2 display all page numbers in the scan)
target - the particular page we are looking for
order - traversal order
(0 descending, 1 ascending)
|
Example:
DBCC TRACEON (3604)
DBCC pglinkage(6,26,0,1,0,1)
|
DBCC procbuf
This command displays procedure buffer headers and stored procedure 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)
|
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)
|
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
|
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)
|
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)
|
Example:
DBCC TRACEON (3604)
dbcc pss
|
DBCC rebuildextents
DBCC rebuildextents rebuilds an object's extent chain. You should set
the READ ONLY option for your database, before you run this command.
Syntax:
DBCC rebuildextents( dbid, objid, indexid )
where
dbid - database ID
objid - object ID
indexid - index ID
|
Example:
DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('pubs')
SELECT @objectid = object_id('authors')
DBCC rebuildextents(@dbid,@objectid,1)
|
DBCC resource
This command shows the server's level RESOURCE, PERFMON and DS_CONFIG
information. 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 information.
Syntax:
DBCC resource
Example:
DBCC TRACEON (3604)
DBCC resource
|
DBCC show_bucket
This command shows hash bucket information for the specified pageid.
Syntax:
DBCC show_bucket( dbid|dbname, pageid, lookup_type )
where
dbid|dbname - database ID or database name
page - logical page number of page being looked for
lookup_type - how to conduct search
(1 use hash algorithm to look in the bucket the page should be in
2 scan the entire buffer cache)
|
Example:
DBCC TRACEON (3604)
DBCC show_bucket (pubs, 1, 1)
|
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):
DBCC tab (dbname, objname, printopt={ 0 | 1 | 2 })
where
dbname - is the database name,
objname - is the table name,
printopt - is the type of the output:
0 - minimum information (only the pages headers, the total
number of data pages in this table and the total number
of data rows in this table)
1 - more information (plus full rows structure)
2 - as printopt = 1, but without rows separation (full dump)
by default printopt = 0
|
Example:
DBCC TRACEON (3604)
DBCC tab (pubs, 'authors')
|
DBCC undo
Syntax:
DBCC undo( dbid, pageno, rowno )
Literature
1. What are all the dbcc commands for SQL Server?
2. INFO: Description of DBCC PAGE Command
3. THE UNAUTHORIZED DOCUMENTATION OF DBCC
4. The Totally Unauthorized List of Sybase DBCC Commands (1)
5. The Totally Unauthorized List of Sybase DBCC Commands (2)
6. The Totally Unauthorized List of Sybase DBCC Commands (3)
7. FIX: Database Usage Count Does Not Return to Zero
8. INFO: Description of the DBCC PGLINKAGE Command
|
|
|
