Some Useful Undocumented SQL Server 6.5 DBCC Commands

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

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

Continues…

Pages: 1 2 3 4 5 6




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |