Some Useful Undocumented SQL Server 6.5 DBCC Commands

DBCC IND 

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

Syntax:

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

where:

dbid|dbname – database id or the database name

objid|objname – object id or the object name

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

This is an 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 the index references is 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.

Syntax:

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

where:

dbid or dbname – Enter either the dbid or the name of the database

type – is the type of output, and includes these options:

0 – minimum information (operation, context, transaction id)

1 – more information (plus flags, tags, row length, description)

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, run the following command:

DBCC log (master)

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:

USE pubsGODBCC TRACEON (3604)
GODECLARE @pgid int
SELECT @pgid = first FROM sysindexes WHERE id = object_id(‘titleauthor’) AND indid = 1DBCC 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………

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 |