DBCC的归纳
总之,DBCC命令所返回的信息能准确地反映数据库及它的各个对象的状态,是我们检测数据库的好帮手。
一 DBCC命令的格式
dbcc
(checktable ((表名|表标识( [, skip_ncindex] ) |
checkdb [(数据库名[, skip_ncindex] )] |
checkalloc [ (数据库名[, fix | nofix] )] |
tablealloc( {表名|表标识}
[,{full |optimized |fast |null}
[, fix |nofix] ]]) |
indexalloc ( {表名|表标识},索引标识
[,{full |optimezed | fast | null}
[, fix |nofix ]] ) |
checkcatalog [ (数据库名)] |
dbrepair(数据库名,dropdb ) |
reindex({表名|表标识} ) |
fix_text({表名|表标识) }
dbcc的权限,对于checktable,fix_text和reindex是缺省赋给表的属主,对于checkdb,checkalloc,checkcatalog,dbrepair,indexalloc和tablealloc,是缺省赋给数据库属主的。DBO自动获得DBCC命令和全部选项的权限。该权限不可转授。此外,dbcc在数据库是活动时运行,除了dbrepair选项和带有fix选项的dbcc checkalloc以外。
checktable选项
checktable是用来对一个指定的表做检查,确保索引和数据页正确地连接,索引按正确的顺序存储,所有指针的一致性,每页上数据信息的合理性,页偏移的合理性。如果日志段在它自己的(日志)设备上,对syslogs表使用dbcc checktable命令可以报告已使用的和剩余的日志空间,使用skip_ncindex选项使得dbcc checktable跳过对用户表上非聚簇索引(nonclustered index)的检查。缺省是检查所有的索引。
例1.检查日志使用的空间量和未用的空间量:
dbcc checktable (syslogs)
若日志段在日志设备上,则会返回如下信息:
checking syslogs
The total number of data page in the table is 1.
NOTICE:Space used on the log segment is 0.20 Mbytes, 0.13%.
NOTICE:Space free on the log segment is 153.4Mbytes,99.87%.
DBCC execution Completed.If dbcc printed error messages,
Contact a user with SA role.
若日志不在它自己的设备上,则会显示下列信息:
NOTICE:Notification of log space used/free.
Can not be reported because the log segment is not on its own device.
例2. dbcc checktable (titles)
The total number of data page in this table is 3.
Table has 18 data rows.
DBCC execution Completed. If DBCC printed error messages. contact a user with SA role.
checkdb选项
运行checkdb选项同checktable检查的内容一样,但它是对一指定数据库中的每张表都做这样的检查。若未指定数据库名,checkdb检查当前的数据库。checkdb返回的信息,也同于checktable。
checkalloc选项
checkalloc是检查指定数据库,看其所有正确分配的页和尚未分配的页的情况。若未指定数据库名,则checkalloc检查当前数据库。checkalloc会返回已分配的和使用的空间数量。checkalloc的缺省模式为nofix,要使用fix选项,必须把数据库置于单用户模式。
例:
dbcc checkalloc (pubs2)
.
.
.
alloc page 0 (#of extent=32 used pages=68 ref pages=68)
alloc page 256 (# of extent=32 used pages=154 ref pages=154)
alloc page 512 (# of extent=28 used pages=184 ref pages=184)
alloc page 768 (# of extent=1 used pages=1 ref pages=1)
total (# of extent=93 used pages=407 ref pages=407) in this database.
DBCC execution completed.If dbcc printed error message,
Contact a user with System Adminstrator (SA) role.
tablealloc选项
tablealloc检查指定的表以确保所有页都被正确地分配。它是checkalloc的缩小版本。对单张表进行相同的完整性检查。使用tablealloc可以生成三种类型的报表:full,optimized和fast。full选项相当于表一级的checkalloc;它报告各种类型的分配错误。optimized选项基于表的对象分配映像(OAM)页里列出的分配页生成报告。它并不报告,也不能整理OAM页里没有列出的在分配页上没有引用的扩展(extent)。如果没有指明类型,或使用了null,则optimized选项是缺省的设置。fast选项,并不生成分配报告,但生成一个被引用但并没有在扩展里分配的页的额外的报告。fix|nofix选项决定tablealloc 是否整理表中发现的分配错误。对于所有的表,缺省为fix,但系统表除外,它们的缺省为nofix。要对系统表使用fix选项,必须首先将数据库置成单用户模式。
例:
dbcc tablealloc(titles)
显示信息如下:
The default report option of OPTIMIZED is used for this run. The default fix option of FIX.is used for this run.
.
.
.
Total #of extent=3
Alloc page 256 (# of extent=1 used pages=2 ref pages=2).
Alloc page 256(# of extent=1 used pages=2 ref pages=2)
Alloc page 256 (# of extent=1 used pages=2 ref pages=2)
Total (# of extent=3 used pages=8 ref pages=8) in this database.
indexalloc 选项
indexalloc检查指定的索引,确保所有的页都被正确地分配,它是checkalloc的缩小版本,对单独一条索引指定同样的完整性检查。其中各选项与tablealloc相同。
checkcatalog选项
checkcatalog选项用于检查系统表内,系统表之间的一致性。例如:它确保在syscolumns表中的每一(数据)类型在systypes表中都有一个相匹配的记录;对于sysobjects中的每个表和视图在syscolumns表中应有关于它们每一列的描述记录;确保在syslogs中的最后一个检查点是有效的。checkcatalog也报告任何已定义的段。若不指定数据库名,则检查当前数据库。
dbrepair选项
dbrepair(数据库名,dropdb)选项是删除一个受破坏的数据库。受破坏的数据库是不能用drop database命令删除的,drop database只能删除正常的数据库,当执行dbrepair命令时,任何用户(包括执行此命令的用户)都不得使用正被删除的数据库。该选项要在master库中运行。
reindex选项
reindex选项通过运行dbcc checktable的“fast”执行方式检查用户表上索引的完整性。如果它检测出索引有问题则会删除并重建索引。在SQL Server的排列顺序改变之后,SA或表属主应该执行这一选项。此选项不能在用户定义的事务中运行。
例:
dbcc reindex (titles)
返回信息:One or more indexes corrupt.They will be rebuilt.
fix_text选项
SQL Server的字符集由单字节转变为多字节后,fix_text选项用于升级文本值。SQL Server的字符集由单字节转变为多字节字符集会使文本数据的管理更加复杂。由于文本值可能较大足以覆盖若干页,SQL Server必须能处理(通过页约束)可能横跨页的字符。为做到这点,服务器需要在每一文本页上添加一些信息。SA或表属主必须在文本数据的每一个表上运行dbcc fix_text,以计算所需要的新页数。
二 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 将表标记为不在内存驻留。将表标记为不在内存驻留后,
可以清空高速缓存中的表页
Some Useful Undocumented SQL Server 7.0 and 2000 DBCC Commands
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 and 2000 for administering and monitoring.
DBCC is an abbreviation for Database Console Command. DBCC commands are generally used to check the physical and logical consistency of a database, although they are also used for a variety of miscellaneous tasks, as you will see here.
Note, the command:
DBCC TRACEON (3604)
is issued before each of the following DBCC examples in order to better demonstrate the effects of the command by displaying a trace of the output of the DBCC command. It is not actually required to run the DBCC commands examined below. If you run any of the DBCC commands below without the above option, the command runs, but you don't see what it is doing.
DBCC BUFFER
This command can be used to display 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, which includes:
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 an example:
DBCC TRACEON (3604)
DBCC buffer(master,'sysobjects')
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 an example:
DBCC TRACEON (3604)
DBCC bytes (10000000, 100)
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 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 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 an example:
DBCC TRACEON (3604)
DECLARE @dbcc_stmt sysname
SELECT @dbcc_stmt = 'CHECKTABLE'
DBCC HELP (@dbcc_stmt)
DBCC IND
Shows all pages in use by indexes of the specified table.
Syntax:
dbcc ind(dbid|dbname, objid|objname, printopt = {-2|-1|0|1|2|3})
where:
dbid|dbname - database id or the database name
objid|objname - object id or the object name
printopt - print option
There is change in this command in how it is used in SQL Server 7.0, in that the printopt parameter is now no longer optional.
This is an example:
DBCC TRACEON (3604)
DBCC IND (master, sysobjects, 0)
DBCC LOG
This command is used to view the transaction log for the specified database.
Syntax:
DBCC log ({dbid|dbname}, [, type={-1|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.
-1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID
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:
DBCC TRACEON (3604)
DBCC PAGE (master, 1, 1)
DBCC PROCBUF
This command displays procedure buffer headers and stored procedure headers from the procedure cache.
Syntax:
DBCC procbuf([dbid|dbname], [objid|objname], [nbufs], [printopt = {0|1}])
where:
dbid|dbname - database id or the database name
objid|objname - object id or the object name
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 an example:
DBCC TRACEON (3604)
DBCC procbuf(master,'sp_help',1,0)
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 an example:
DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('master')
SELECT @objectid = object_id('sysobjects')
DBCC prtipage(@dbid,@objectid,1,0)
DBCC PSS
This command shows info about processes currently connected to the server.
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 an example:
DBCC TRACEON (3604)
DBCC pss
DBCC RESOURCE
This command shows the server's level RESOURCE, PERFMON and DS_CONFIG information. 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 information.
Syntax:
DBCC resource
This is an example:
DBCC TRACEON (3604)
DBCC resource
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 an example:
DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('master')
SELECT @objectid = object_id('sysdatabases')
DBCC TAB (@dbid,@objectid)