Some Useful Undocumented SQL Server 6.5 DBCC Commands

DBCC DBINFO 

Displays DBINFO structure for the specified database.

Syntax:

DBCC DBINFO [(dbname)]

where:

dbname – is the database name

This is an example:

DBCC TRACEON (3604)
DBCC DBINFO (master)

DBCC DBTABLE 

This command displays the contents of the DBTABLE structure.

Syntax:

DBCC DBTABLE ({dbid|dbname})

where:

dbid|dbname – database name or database ID

This is an example:

DBCC TRACEON (3604)
DBCC DBTABLE (master)

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

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 nonloged command, so you can delete a particular row without placing 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 – specify how to 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

Prints the contents of the specified DES (descriptor).

Syntax:

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

where:

dbid|dbname – database id or the database name
objid|objname – object id or the object name

This is an example:

DBCC TRACEON (3604)
DBCC DES

DBCC EXTENTCHAIN

This command displays extent header info for all the 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’)
DVCC EXTENTCHAIN(@dbid,@objectid,0,0,0,0)

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 |