- 论坛徽章:
- 0
|
DBCC详解
2009-05-02 17:27:44
标签：sql server
一、 数据库一致性检查工具（Database Consistenecy Checker，简称DBCC）。DBCC是一个实用命令集，用来检查一个数据库的逻辑一致性及物理一致性。在开发和应用中，DBCC是我们经常要使用的命令。
数据库控制台命令语句被分为以下类别。
维护
对数据库、索引或文件组进行维护的任务。
杂项
杂项任务，如启用跟踪标志或从内存中删除 DLL。
信息
收集并显示各种类型信息的任务。
验证
对数据库、表、索引、目录、文件组或数据库页的分配进行的验证操作。
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）的检查。缺省是检查所有的索引。
DBCC 语句使用输入参数和返回值。所有 DBCC 语句参数都可以接受 Unicode
和 DBCS 字面值。
使用 DBCC 结果集输出
许多 DBCC 命令可以产生表格格式的输出（使用 WITH TABLERESULTS 选项）。该信息可装载到表中以便将来使用。
例１.检查日志使用的空间量和未用的空间量：
dbcc checktable (syslogs)
若日志段在日志设备上，则会返回如下信息：
SysLogs的 DBCC 结果。
对象 'SysLogs' 的 37 页中有 4361 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
若日志不在它自己的设备上，则会显示下列信息：
消息 2501，级别 16，状态 45，第 1 行
找不到名为 "syslogs" 的表或对象。请检查系统目录。
checkdb选项
运行checkdb选项同checktable检查的内容一样，但它是对一指定数据库中的每张表都做这样的检查。若未指定数据库名，checkdb检查当前的数据库。checkdb返回的信息，也同于checktable。
checkalloc选项
checkalloc是检查指定数据库，看其所有正确分配的页和尚未分配的页的情况。若未指定数据库名，则checkalloc检查当前数据库。checkalloc会返回已分配的和使用的空间数量。checkalloc的缺省模式为nofix，要使用fix选项，必须把数据库置于单用户模式。
例：
dbcc checkalloc (iccard13)
……
在此数据库中，总区数 = 325，已用页数 = 2534，保留页数 = 2589。
此数据库中(混合区数 = 45，混合页数 = 349)。
CHECKALLOC 在数据库 'IcCard13' 中发现 0 个分配错误和 0 个一致性错误。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
tablealloc选项
tablealloc检查指定的表以确保所有页都被正确地分配。它是checkalloc的缩小版本。对单张表进行相同的完整性检查。使用 tablealloc可以生成三种类型的报表：full,optimized和fast。full选项相当于表一级的checkalloc；它报告各种类型的分配错误。optimized选项基于表的对象分配映像（OAM）页里列出的分配页生成报告。它并不报告，也不能整理OAM页里没有列出的在分配页上没有引用的扩展（extent）。如果没有指明类型，或使用了null，则optimized选项是缺省的设置。fast选项，并不生成分配报告，但生成一个被引用但并没有在扩展里分配的页的额外的报告。fix|nofix选项决定tablealloc 是否整理表中发现的分配错误。对于所有的表，缺省为fix，但系统表除外，它们的缺省为nofix。要对系统表使用fix选项，必须首先将数据库置成单用户模式。
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或表属主应该执行这一选项。此选项不能在用户定义的事务中运行。
fix_text选项
SQL Server的字符集由单字节转变为多字节后，fix_text选项用于升级文本值。SQL Server的字符集由单字节转变为多字节字符集会使文本数据的管理更加复杂。由于文本值可能较大足以覆盖若干页，SQL Server必须能处理（通过页约束）可能横跨页的字符。为做到这点，服务器需要在每一文本页上添加一些信息。SA或表属主必须在文本数据的每一个表上运行dbcc fix_text，以计算所需要的新页数。
总之，DBCC命令所返回的信息能准确地反映数据库及它的各个对象的状态。
二、DBBCC维护语句：对数据库、索引或文件组进行维护的任务
1、DBCC SHRINKDATABASE --压缩整个数据库的数据文件与日志文件。
例：DBCC SHRINKDATABASE(adventureworks,40) --将此数据库压缩到仅剩下40%的剩余空间。
……DBCC SHRINKDATABASE: 已跳过数据库 ID 6 的文件 ID 1，因为该文件没有足够的可用空间可以回收。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
2、DBCC SHRINKFILE --压缩指定的数据库文件或记录文件的大小。文件存于sys.database_file的命令行
DBCC SHRINKFILE('AdventureWorks_log',50) --将指定的文件压缩到仅剩下50M
3、DBCC UPDATEUSAGE --更正任何无效的命令行或页面计数，对象可从整个数据库到单一的数据表或是索引，多用在数据库升级后的处理操作。
如：DBCC UPDATEUSAGE(0)
4、DBCC CLEANTABLE。回收删除的可变长度列和文本列的空间。
如：
use iccard13
go
DBCC CLEANTABLE(0,log,0)WITH NO_INFOMSGS
5、DBCC INDEXDEFRAG。指定表或视图的索引碎片整理。
6、DBCC DBREINDEX。 对指定数据库中的表重新生成一个或多个索引。
7、DBCC DROPCLEANBUFFERS。 从缓冲池中删除所有清除缓冲区。
8、DBCC FREEPROCCACHE。 从过程缓存中删除所有元素。
三、DBBCC验证语句：对数据库、表、索引、目录、文件组或数据库页的分配进行的验证操作
DBCC CHECKALLOC。检查指定数据库的磁盘空间分配结构的一致性。
DBCC CHECKFILEGROUP。检查当前数据库中指定文件组中的所有表的分配和结构完整性。
DBCC CHECKCATALOG。检查指定数据库内的目录一致性。数据库必须联机。
DBCC CHECKIDENT。 检查指定表的当前标识值，如有必要，则更改标识值。
DBCC CHECKCONSTRAINTS。 检查当前数据库中指定表上的指定约束或所有约束的完整性。
DBCC CHECKTABLE。检查组成表或索引视图的所有页和结构的完整性。
DBCC CHECKDB。检查指定数据库中所有对象的分配、结构和逻辑完整性。
四、DBBCC的信息语句
DBCC SHOW_STATISTICS。显示指定表上的指定目标的当前分发统计信息。
DBCC INPUTBUFFER.显示从客户端发送到 Microsoft SQL Server 2005 实例的最后一个语句。
DBCC INPUTBUFFER ( session_id [ , request_id ] ) [WITH NO_INFOMSGS ]
DBCC SHOWCONTIG.显示指定的表的数据和索引的碎片信息。
DBCC OPENTDBCC INPUTBUFFERRAN 如果在指定数据库内存在最早的活动事务和最早的分布式和非分布式复制事务，则显示与之有关的信息
DBCC SQLPERF.提供有关如何在所有数据库中使用事务日志空间的统计信息。
DBCC SQLPERF ( LOGSPACE | 'sys.dm_os_latch_stats' , CLEAR | 'sys.dm_os_wait_stats' , CLEAR )
[WITH NO_INFOMSGS ]
DBCC OUTPUTBUFFER.以十六进制和 ASCII 格式返回指定 session_id 的当前输出缓冲区。DBCC OUTPUTBUFFER ( session_id [ , request_id ] )
DBCC TRACESTATUS.显示跟踪标志的状态.DBCC TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] )
DBCC PROCCACHE.以表格格式显示有关过程缓存的信息。DBCC PROCCACHE [ WITH NO_INFOMSGS ]
DBCC USEROPTIONS 返回当前连接的活动（设置）的 SET 选项。DBCC USEROPTIONS
五、DBBCC的杂项语句：杂项任务，如启用跟踪标志或从内存中删除 DLL
DBCC HELP。返回指定的 DBCC 命令的语法信息。DBCC HELP ( 'dbcc_statement' | @dbcc_statement_var | '?' )[ WITH NO_INFOMSGS ]
DBCC dllname (FREE)。从内存中上载指定的扩展存储过程 DLL。DBCC dllname ( FREE ) [ WITH NO_INFOMSGS ]
DBCC DBREPAIR 。禁用指定的跟踪标记。DBCC TRACEOFF ( trace# [ ,...n ] [ , -1 ] ) [ WITH NO_INFOMSGS ]
DBCC TRACEON。启用指定的跟踪标记。DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]
六、未公开的DBCC
DBCC ERRLOG 初始化SQL错误日志
DBCC BUFFER 显示缓冲区头部和页面信息
DBCC FLUSHPROCINDB 清楚数据库服务器内存中的某个数据库存储过程的缓存内容。
DBCC DBINFO 显示数据库结果信息
DBCC DATABLE 显示管理数据库的表信息
DBC IND 查看某个索引使用的页面信息。
DBCC REBULDLOG
重建修复SQL数据库事物日志文件。
DBCC LOG 查看某个数据库的事务日志信息
DBCC PAGE 查看某个数据库数据也面信息
DBCC PROCBUF 显示过程缓冲池的缓冲区头和存储过程。
DBCC PRTIPAGE 查看某个索引页面的每行指向的页面号。
DBCC PSS 显示当前连接到SQLSERVER服务器的进程信息。
DBCC RESOURCE 显示服务器当前使用的资源情况。
DBCC TAB 查看数据页面的结构
我们知道，在数据库系统的开发和应用中，必须保证数据库的完整性和一致性。
当数据库出现了严重错误；当我们怀疑数据库受到破坏（如无法用drop命令删除数据库或对象，使用某个表时出现“不可靠数据”的信息等）；当用户改变了 Server的缺省排序的顺序或改变了字符集而需要检查；当SA对系统做定期检查；这些时候，我们都需要使用数据库一致性检查工具（Database Consistenecy Checker，简称DBCC）。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）的检查。缺省是检查所有的索引。
例１.检查日志使用的空间量和未用的空间量：
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=6
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= 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命令所返回的信息能准确地反映数据库及它的各个对象的状态，是我们检测数据库的好帮手。
sqlserver 事务日志文件格式 收藏
以前有个log explorer能读日志,但不支持2008
所以找了篇事务日志文件格式文章,留待研究
http://book.51cto.com/art/201001/181081.htm
当日志文件大小超过预期的时候，数据库管理员自然会想去看看日志文件中到底存放了些什么信息。SQL Server有一条"DBCC LOG"命令可以帮助我们解释日志文件中的信息。它的语法是：
DBCC LOG(, )
：目标数据库编号。可以用sp_helpdb得到。
：DBCC LOG命令翻译和解释日志记录的方式。
一般来讲，使用"3"这个格式参数输出比较详细。
下面我们通过一个很简单的表格操作来看看SQL Server是怎么组织事务日志记录的。
首先，我们在范例数据库AdventureWorks里面创建一个只有一个int类型字段的表格。然后将数据库日志文件清空。接着运行DBCC LOG命令。找到这时日志文件的最后一条记录。
use adventureworks
go
create table a (a int)
go
checkpoint
go
backup log adventureworks WITH NO_LOG
--2008中需改为 DBCC SHRINKFILE (N'adventureworks_Log' , 1)
go
dbcc log(adventureworks,3)
go
--接着，我们在表格里插入一条记录。
insert into a values (1)
go
dbcc log(adventureworks,3)
go
结果中（见图1-26）可以看到三条关于这个Insert的记录。
--sql2008中增加了20几条记录
图1-26 DBCC LOG结果中3条和INSERT动作相关的记录
我们再插一条记录。
1. insert into a values (100)
2. go
3. dbcc log(5,3)
4. go
可以看到新的3条记录（见图1-27）。新的记录有不同的LSN编号。
图1-27 新的3条和INSERT动作相关的记录
从这些记录里，我们可以看到刚才做的INSERT的事务，它的起始时间，刚才连接的SPID，以及其他一些信息。SQL Server完全可以通过这些记录把INSERT重做，或者撤销。
我们把这两条记录都改成2。这次出现了6条记录（见图1-28）。
1. update a set a = 2
2. go
图1-28 和UPDATE动作相关的6条记录
从这些记录可以看出，虽然只是一条UPDATE语句，但是实际上SQL Server并没有记录语句本身。它记录的是两条被修改的数据原来的值和现在的值。
因此我们可以发现，SQL Server的日志记录有以下特点：
1. 日志记录的是数据的变化，而不是记录用户发过来的操作。
在这一点上，日志记录的定位很清楚。它只是为了保证数据库一致性。所以它记录的信息对SQL Server来讲很有意义。但是如果想要通过它来倒推出用户刚才发过来的语句，可以说是不可能的。
2. 每条记录都有它唯一的编号（LSN），并且记录了它属于的事务号。
这种设计便于事务的重新提交和回滚。
3. 日志记录的行数和实际修改的数据量有关。
SQL Server会为每一条记录的修改保存日志记录。如果单个语句修改的行数非常多，那它所带来的日志行数也就会非常多。所以日志增长的速度不仅和事务的多少有关，还和事务所带来的数据的修改量有关。
4. 日志记录了事务发生的时间，但是不保证记录下了发起这个事务的用户名，更不记录发起者的程序名称。
5. SQL Server能够从日志记录里面读到数据修改前的值和修改后的值。但是对管理者来讲，直接从日志记录里面是很难了解其修改过程的。
讨论这些的原因，是因为很多用户希望能从日志文件里倒推出数据库曾经发生的异常操作。比如，是谁恶意或不小心删掉了一些重要数据，或者是谁在某个时间段发起了一个庞大的事务。由于SQL Server日志定位不是做用户行为监视和记录，而是在对性能影响最小的前提下保证事务一致性，所以它记录的内容是面向数据库服务，而不是面向用户的。换句话说，它记录的东西只要SQL Server自己能读懂就可以了，而没有考虑要给用户去访问和理解。所以使用者很难用事务日志来达到倒推的目的。
市场上有一些第三方的工具宣称能从SQL Server的日志文件中完成一些推断工作。他们能够更进一步地解释日志记录，并且做一些自动化的工作（例如，帮助用户回滚一个误操作）。但是如果有什么内容DBCC LOG看不到，这些工具应该也很难看到。所以如果要监视用户的行为，还是要开启SQL Server自己的监视工具，比如SQL Trace或XEvents等。 |
|
免费注册
发表于 2011-05-06 20:02
发表于 2011-05-06 22:19
