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………
…