XP与DBCC

本文主要是介绍XP与DBCC，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

维护语句

DBCC DBREINDEX 重建指定数据库中表的一个或多个索引
DBCC DBREPAIR 除去损坏的数据库
DBCC INDEXDEFRAG 整理指定的表或视图的聚集索引和辅助索引碎片
DBCC SHRINKDATABASE 收缩指定数据库中的数据文件大小
DBCC SHRINKFILE 收缩相关数据库的指定数据文件或日志文件大小
DBCC UPDATEUSAGE 报告和更正 sysindexes 表的不正确内容，
该内容可能会导致通过 sp_spaceused
系统存储过程产生不正确的空间使用报表

状态语句
DBCC INPUTBUFFER 显示从客户端发送到MS SQL Server 的最后一个语句
DBCC OPENTRAN 如果在指定数据库内存在最旧的活动事务和最旧的分布和非分布式复制事务，
则显示与之相关的信息。只有当存在活动事务或数据库包含复制信息时，
才显示结果。如果没有活动事务，就显示信息性消息
DBCC OUTPUTBUFFER 以十六进制或 ASCII 格式返回指定系统进程 ID (SPID) 的当前输出缓冲区
DBCC PROCCACHE 以报表形式显示有关过程高速缓存的信息
DBCC SHOWCONTIG 显示指定的表的数据和索引的碎片信息
DBCC SHOW_STATISTICS 显示指定表上的指定目标的当前分布统计信息
DBCC SQLPERF 提供有关所有数据库中的事务日志空间使用情况的统计信息
DBCC TRACESTATUS 显示跟踪标记的状态
DBCC USEROPTIONS 返回当前连接的活动（设置）的 SET 选项

验证语句
DBCC CHECKALLOC 检查指定数据库的磁盘空间分配结构的一致性
DBCC CHECKCATALOG 检查指定数据库中的系统表内及系统表间的一致性
DBCC CHECKCONSTRAINTS 检查指定表上的指定约束或所有约束的完整性
DBCC CHECKDB 检查指定数据库中的所有对象的分配和结构完整性
DBCC CHECKFILEGROUP 检查指定文件组中的所有表（在当前数据库中）的分配和结构完整性
DBCC CHECKIDENT 检查指定表的当前标识值，如有必要，还对标识值进行更正DBCC CHECKTABLE
检查指定表或索引视图的数据、索引及 text、ntext 和 image 页的完整性
DBCC NEWALLOC 检查数据库的扩展结构内的每个表的数据和索引页的分配

其他语句
DBCC dllname (FREE) 从内存中卸载指定的扩展存储过程动态链接库 (DLL)
DBCC HELP 返回指定的 DBCC 语句的语法信息
DBCC PINTABLE 将表标记为驻留，这表示MS SQL Server不从内存中刷新表页
DBCC ROWLOCK 在MS SQL Server 6.5 版中使用，对表启用插入行锁定 (IRL) 操作
DBCC TRACEOFF 禁用指定的跟踪标记
DBCC TRACEON 打开（启用）指定的跟踪标记
DBCC UNPINTABLE 将表标记为不在内存驻留。将表标记为不在内存驻留后，
可以清空高速缓存中的表页

llocdump( dbid, page )
bhash( { print_bufs | no_print }, bucket_limit )
buffer( [ dbid ][, objid ][, nbufs ], printopt={ 0 | 1 | 2 }, buftype )
bytes( startaddress, length )
checkalloc[( dbname [, fix | nofix ] ) ]
checkcatalog[( dbname )]
checkdb[( dbname [, skip_ncindex ] ) ]
checktable( tablename | tabid [, skip_ncindex ] )
dbinfo( [ dbname ] )
dbrepair( dbid, option={ dropdb | fixindex | fixsysindex }, table, indexid )
dbtable( dbid )
delete_row( dbid, pageid, delete_by_row={ 1 | 0 }, rownum )
des( [ dbid ][, objid ] )
extentcheck( dbid, objid, indexid, sort={1|0} )
extentchain( dbid, objid, indexid, sort={1|0},display(1,0) )
extentdump( dbid, page )
extentzap( dbid, objid, indexid, sort )
findnotfullextents( dbid, objid, indexid, sort={ 1 | 0 } )
fix_al( [ dbname ] )
help( dbcc_command )
ind( dbid, objid, printopt={ 0 | 1 | 2 } )
indexalloc(tablename|tabid, indid, [full | optimized | fast],[fix | nofix])
locateindexpgs( dbid, objid, page, indexid, level )
lock
log( [dbid][,objid][,page][,row][,nrecords][,type={-1..36}],printopt={0|1} )
memusage
netmemshow( option={1 | 2 | 3} )
netmemusage
newalloc( dbname, option={ 1 | 2 | 3 } )
page( dbid, pagenum [, printopt={0|1|2} ][, cache={0|1} ][, logical={1|0} ] )
pglinkage( dbid, start, number, printopt={0|1|2}, target, order={1|0} )
pktmemshow( option={spid} )
procbuf( dbid, objid, nbufs, printopt={ 0 | 1 } )
prtipage( dbid, objid, indexid, indexpage )
pss( suid, spid, printopt={ 1 | 0 } )
rebuildextents( dbid, objid, indexid )
resource
show_bucket( dbid, pageid, lookup_type )
tab( dbid, objid, printopt={ 0 | 1 | 2 } )
tablealloc(tablename|tabid, [full | optimized | fast],[fix | nofix])
traceoff( tracenum [, tracenum ... ] )
traceon( tracenum [, tracenum ... ] )
undo( dbid, pageno, rowno )
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Introduction
Undocumented DBCC commands:
DBCC BUFFER
DBCC BYTES
DBCC DBINFO
DBCC DBTABLE
DBCC DES
DBCC HELP
DBCC IND
DBCC LOG
DBCC PAGE
DBCC PROCBUF
DBCC PRTIPAGE
DBCC PSS
DBCC RESOURCE
DBCC TAB
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 7.0
for administering and monitoring.

DBCC is an abbreviation of a DataBase Consistency Checker.
This is the description of DBCC from SQL Server Books Online:

A statement used to check the logical and physical consistency of a
database, check memory usage, decrease the size of a database, check
performance statistics, and so on. Database consistency checker (DBCC)
ensures the physical and logical consistency of a database, but is not
corrective.

Top

5 楼j9988（j9988）回复于 2002-12-16 22:12:04 得分 0

Undocumented DBCC commands
1. DBCC BUFFER

This command can be used to print buffer headers and pages from the
buffer cache.
Syntax:

dbcc buffer ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])

where dbid|dbname - database id|database name.
objid|objname - object id|object name
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

This is the example:

DBCC TRACEON (3604)
dbcc buffer(pubs,'sysobjects')

2. 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

This is the example:

DBCC TRACEON (3604)
dbcc bytes (1000000, 100)

3. DBCC DBINFO

Print DBINFO structure for specified database.

DBCC DBINFO [( dbname )]

where
dbname - is the database name.

This is the example:

DBCC TRACEON (3604)
DBCC DBINFO (pubs)

4. DBCC DBTABLE

This command prints out the contents of the DBTABLE structure.
Syntax:

DBCC DBTABLE ({dbid|dbname})

where
dbid|dbname - database name or database ID

This is the 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

5. DBCC DES

Prints the contents of the specified DES (descriptor).
Syntax:

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

where dbid|dbname - database id|database name.
objid|objname - object id|object name

This is the example:

DBCC TRACEON (3604)
DBCC DES

6. DBCC HELP

DBCC HELP returns syntax information for the specified DBCC statement.
In comparison with DBCC HELP command in version 6.5, it returns syntax
information only for the documented DBCC commands.

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)

Look at here for more details:
DBCC HELP
(T-SQL)

7. DBCC IND

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

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

where
dbid|dbname - database id|database name.
objid|objname - object id|object name
printopt - print option

This is the example:

DBCC TRACEON (3604)
DBCC IND (pubs, authors)

8. DBCC log

This command is used to view the transactional log for the specified
database.

DBCC log ( {dbid|dbname}, [, type={-1|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.

-1 - full information about each operation plus hexadecimal dump
of the current transaction log's row, plus Checkpoint Begin,
DB Version, Max XACTID

by default type = 0

To view the transaction log for the master database, you can run the
following command:

DBCC log (master)

9. 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.

Top

6 楼j9988（j9988）回复于 2002-12-16 22:12:12 得分 0

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 table titleauthor,
database pubs.

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

10. DBCC procbuf

This command prints procedure buffer headers and proc-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)

This is the 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)

11. 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

This is the 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)

12. DBCC pss

This command shows info about processes currently connected to the
dataserver. Structure is the source of data contained in the sysprocesses
table.

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)

This is the example:

DBCC TRACEON (3604)
dbcc pss

13. DBCC resource

This command shows dataserver level RESOURCE, PERFMON and DS_CONFIG
info. 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 info.

Syntax:

DBCC resource

This is the example:

DBCC TRACEON (3604)
DBCC resource

14. 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)

Syntax:

DBCC tab (dbid, objid)

where
dbid - is the database id
objid - is the table id

This is the example:

DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('pubs')
SELECT @objectid = object_id('authors')
DBCC TAB (@dbid,@objectid)

Literature
1. "What are all the dbcc commands for SQL Server?" NTFAQ
http://www.ntfaq.com/Articles/Index.cfm?ArticleID=14186

2. INFO: Description of DBCC PAGE Command
http://support.microsoft.com/support/kb/articles/Q83/0/65.ASP

3. THE UNAUTHORIZED DOCUMENTATION OF DBCC
http://user.icx.net/~huntley/dbccinfo.htm

4. The Totally Unauthorized List of Sybase DBCC Commands
http://www.kaleidatech.com/dbcc1.htm

5. The Totally Unauthorized List of Sybase DBCC Commands
http://www.kaleidatech.com/dbcc2.htm

6. The Totally Unauthorized List of Sybase DBCC Commands
http://www.kaleidatech.com/dbcc3.htm

7. FIX: Database Usage Count Does Not Return to Zero
http://support.microsoft.com/support/kb/articles/Q175/3/03.ASP

8. DBCC HELP (T-SQL)
http://msdn.microsoft.com/library/psdk/sql/dbcc_10.htm
Top

7 楼pengdali（）回复于 2002-12-16 22:16:27 得分 0

@_@!

汗！Top

8 楼j9898（j9988）回复于 2002-12-16 22:24:03 得分 0

Useful Undocumented SQL Server
Extended Stored Procedures

****　　　sp_helpextendedproc　　********

by Alexander Chigrik

An extended stored procedure (xp) is a dynamic link library that runs directly in the address space of SQL Server and is programmed using the SQL Server Open Data Services API. You can run extended stored procedures from the Query Analyzer, for example, just as you would normal stored procedures. Extended stored procedures are used to extend the capabilities of SQL Server. You can take advantage of the many extended stored procedures that come with SQL Server, or you can can write your own in a programming language such as C or C++.

In this article, I want to tell you about some useful undocumented extended stored procedures. These extended stored procedures work with SQL Server 7.0, as well as with SQL Server 2000.

sp_MSgetversion
This extended stored procedure can be used to get the current version of Microsoft SQL Server. To get the current SQL Server version, run:

EXEC master..sp_MSgetversion

Note. A more common way to retrieve the current SQL Server version (this way provides more information) is to use following SELECT statement:

SELECT @@version

xp_dirtree
This extended stored procedure can be used to get a list of all the folders for the folder named in the xp. To get a list of all the folders in the C:/MSSQL7 folder, run:

EXEC master..xp_dirtree 'C:/MSSQL7'

xp_subdirs
This extended stored procedure is used to get the list of folders for the folder named in the xp. In comparison with xp_dirtree, xp_subdirs returns only those directories whose depth = 1.

This is the example:

EXEC master..xp_subdirs 'C:/MSSQL7'

xp_enum_oledb_providers
This extended stored procedure is used to list of all the available OLE DB providers. It returns Provider Name, Parse Name and Provider Description. To get a list of all OLE DB providers for your SQL Server, run:

EXEC master..xp_enum_oledb_providers

xp_enumcodepages
This extended stored procedure can be used to list of all code pages, character sets and their description for your SQL Server. To see this, list, run:

EXEC master..xp_enumcodepages

xp_enumdsn
This extended stored procedure returns a list of all system DSNs and their descriptions. To get the list of system DSNs, run:

EXEC master..xp_enumdsn

xp_enumerrorlogs
This extended stored procedure returns the list of all error logs with their last change date. To get the list of error logs, run:

EXEC master..xp_enumerrorlogs

xp_enumgroups
This extended stored procedure returns the list of Windows NT groups and their description. To get the list of the Windows NT groups, run:

EXEC master..xp_enumgroups

xp_fileexist
You can use this extended stored procedure to determine whether a particular file exists on the disk or not. The syntax for this xp is:

EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]

For example, to check whether the file boot.ini exists on disk c: or not, run:

EXEC master..xp_fileexist 'c:/boot.ini'

xp_fixeddrives
This very useful extended stored procedure returns the list of all hard drives and the amount of free space in Mb for each hard drive. To see the list of drives, run:

EXEC master..xp_fixeddrives

xp_getnetname
This extended stored procedure returns the WINS name of the SQL Server that you're connected to. To view the name, run:

EXEC master..xp_getnetname

xp_readerrorlog
This extended stored procedure returns the content of the errorlog file. You can find the errorlog file in the C:/MSSQL7/Log directory, by default. To see the text of the errorlog file, run:

EXEC master..xp_readerrorlog

xp_regdeletekey
This extended stored procedure will delete an entire key from the registry. You should use it very carefully. The syntax is:

EXECUTE xp_regdeletekey [@rootkey=]'rootkey', [@key=]'key'

For example, to delete the key 'SOFTWARE/Test' from 'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regdeletekey @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE/Test'

xp_regdeletevalue
This extended stored procedure will delete a particular value for a key in the registry. You should use it very carefully. The syntax is:

EXECUTE xp_regdeletevalue [@rootkey=]'rootkey', [@key=]'key', [@value_name=]'value_name'

For example, to delete the value 'TestValue' for the key 'SOFTWARE/Test' from 'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regdeletevalue @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE/Test', @value_name='TestValue'

xp_regread
This extended stored procedure is used to read from the registry. The syntax is:

EXECUTE xp_regread [@rootkey=]'rootkey', [@key=]'key' [, [@value_name=]'value_name'] [, [@value=]@value OUTPUT]

For example, to read into the variable @test from the value 'TestValue' from the key 'SOFTWARE/Test' from the 'HKEY_LOCAL_MACHINE', run:

DECLARE @test varchar(20)EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE/Test', @value_name='TestValue', @value=@test OUTPUTSELECT @test

xp_regwrite
This extended stored procedure is used to write to the registry. The syntax is:

EXECUTE xp_regwrite [@rootkey=]'rootkey', [@key=]'key', [@value_name=]'value_name', [@type=]'type', [@value=]'value'

For example, to write the variable 'Test' to the 'TestValue' value, key 'SOFTWARE/Test', 'HKEY_LOCAL_MACHINE', run:

EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE/Test', @value_name='TestValue', @type='REG_SZ', @value='Test'

Keep in mind that these undocumented extended stored procedures are not officially supported by Microsoft, and that they may not be found in the next version of SQL Server.

Published with the express written permission of the author. Copyright 2001 Alexander Chigrik.
Top