DBCC CHECKALLOC (Transact-SQL)DBCC CHECKALLOC (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

检查指定数据库的磁盘空间分配结构的一致性。Checks the consistency of disk space allocation structures for a specified database.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

DBCC CHECKALLOC   
[  
    ( database_name | database_id | 0   
      [ , NOINDEX   
      | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]  
    )  
    [ WITH   
        {   
          [ ALL_ERRORMSGS ]  
          [ , NO_INFOMSGS ]   
          [ , TABLOCK ]   
          [ , ESTIMATEONLY ]   
        }  
    ]  
]  

参数Arguments

database_name | database_id | 0database_name | database_id | 0
要检查分配和页使用情况的数据库的名称或 ID。The name or the ID of the database for which to check allocation and page usage. 如果未指定,或者指定为 0,则使用当前数据库。If not specified, or if 0 is specified, the current database is used. 数据库名必须遵循有关标识符的规则。Database names must follow the rules for identifiers.

NOINDEXNOINDEX
指定不检查用户表的非聚集索引。Specifies that nonclustered indexes for user tables should not be checked.
维护 NOINDEX 只是为了实现向后兼容性,并不会影响 DBCC CHECKALLOC。NOINDEX is maintained for backward compatibility only and does not affect DBCC CHECKALLOC.

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILDREPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
指定 DBCC CHECKALLOC 修复找到的错误。Specifies that DBCC CHECKALLOC repair the found errors. database_name 必须处于单用户模式 。database_name must be in single-user mode.

REPAIR_ALLOW_DATA_LOSSREPAIR_ALLOW_DATA_LOSS
试图修复找到的任何错误。Tries to repair any errors that are found. 这些修复可能会导致一些数据丢失。These repairs can cause some data loss. REPAIR_ALLOW_DATA_LOSS 是允许修复分配错误的唯一选项。REPAIR_ALLOW_DATA_LOSS is the only option that allows for allocation errors to be repaired.

REPAIR_FASTREPAIR_FAST
保留语法只是为了向后兼容。Syntax is maintained for backward compatibility only. 未执行修复操作。No repair actions are performed.

REPAIR_REBUILDREPAIR_REBUILD
不适用。Not applicable.
仅将 REPAIR 选项作为最后手段使用。Use the REPAIR options only as a last resort. 若要修复错误,建议您通过备份进行还原。To repair errors, we recommend restoring from a backup. 修复操作不会考虑表本身或表之间可能存在的任何约束。Repair operations do not consider any of the constraints that may exist on or between tables. 如果指定的表与一个或多个约束有关,建议您在修复操作后运行 DBCC CHECKCONSTRAINTS。If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. 如果必须使用 REPAIR,则运行不带有修复选项的 DBCC CHECKDB 来查找要使用的修复级别。If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. 如果使用 REPAIR_ALLOW_DATA_LOSS 级别,则建议您在运行带有此选项的 DBCC CHECKDB 之前备份数据库。If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.

替换为WITH
启用要指定的选项。Enables options to be specified.

ALL_ERRORMSGSALL_ERRORMSGS
显示所有错误消息。Displays all error messages. 默认情况下显示所有错误消息。All error messages are displayed by default. 指定或省略此选项都不起作用。Specifying or omitting this option has no effect.

NO_INFOMSGSNO_INFOMSGS
取消所有信息性消息和关于所用空间的报告。Suppresses all informational messages and the report of space used.

TABLOCKTABLOCK
使 DBCC 命令获取排他数据库锁。Causes the DBCC command to obtain an exclusive database lock.

ESTIMATE ONLYESTIMATE ONLY
显示当指定所有其他选项时运行 DBCC CHECKALLOC 所需的估计 tempdb 空间大小。Displays the estimated amount of tempdb space that is required to run DBCC CHECKALLOC when all the other options are specified.

RemarksRemarks

DBCC CHECKALLOC 将检查数据库中所有页的分配,而不管其所属的页类型或对象类型。DBCC CHECKALLOC checks the allocation of all pages in the database, regardless of the type of page or type of object to which they belong. 它还可验证各种内部结构,这些结构可用于跟踪这些页以及它们之间的关系。It also validates the various internal structures that are used to keep track of these pages and the relationships between them. 如果未指定 NO_INFOMSGS,则 DBCC CHECKALLOC 将收集有关数据库中所有对象的空间使用情况信息。If NO_INFOMSGS is not specified, DBCC CHECKALLOC collects space usage information for all objects in the database. 将这一信息与找到的任何错误一起进行打印。This information is printed together with any errors that are found.

备注

DBCC CHECKALLOC 功能包含在 DBCC CHECKDBDBCC CHECKFILEGROUP中。The DBCC CHECKALLOC functionality is included in DBCC CHECKDB and DBCC CHECKFILEGROUP. 这意味着您不必将 DBCC CHECKALLOC 与这些语句分开运行。This means that you do not have to run DBCC CHECKALLOC separately from these statements. DBCC CHECKALLOC 不会检查 FILESTREAM 数据。DBCC CHECKALLOC does not check FILESTREAM data. FILESTREAM 在文件系统中存储二进制大型对象 (BLOB)。FILESTREAM stores binary large objects (BLOBS) on the file system.

内部数据库快照Internal Database Snapshot

DBCC CHECKALLOC 可使用内部数据库快照来提供执行这些检查所需的事务的一致性。DBCC CHECKALLOC uses an internal database snapshot to provide the transactional consistency that it needs to perform these checks. 如果无法创建快照,或指定了 TABLOCK,则 DBCC CHECKALLOC 将尝试获取排他 (X) 数据库锁,以获取所需的一致性。If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKALLOC tries to acquire an exclusive (X) lock on the database to obtain the required consistency.

备注

对 tempdb 运行 DBCC CHECKALLOC 不会执行任何检查。Running DBCC CHECKALLOC against tempdb does not perform any checks. 这是因为,为了提高性能,不允许对 tempdb 使用数据库快照。This is because, for performance reasons, database snapshots are not available on tempdb. 这意味着,无法获得所需的事务一致性。This means that the required transactional consistency cannot be obtained. 停止和启动 MSSQLSERVER 服务可以解决任何 tempdb 分配问题。Stop and start the MSSQLSERVER service to resolve any tempdb allocation issues. 此操作将删除并重新创建 tempdb 数据库。This action drops and re-creates the tempdb database.

了解 DBCC 错误消息Understanding DBCC Error Messages

DBCC CHECKALLOC 命令完成后,会将一条消息写入 SQL ServerSQL Server 错误日志。After the DBCC CHECKALLOC command finishes, a message is written to the SQL ServerSQL Server error log. 如果 DBCC 命令成功执行,则消息指示成功完成以及命令运行的时间。If the DBCC command successfully executes, the message indicates a successful completion and the amount of time that the command ran. 如果 DBCC 命令在完成检查之前由于错误而停止,则消息将指示命令已终止,并指示状态值和命令运行的时间。If the DBCC command stops before completing the check because of an error, the message indicates the command was terminated, a state value, and the amount of time the command ran. 下表列出并说明了此消息中可包含的状态值。The following table lists and describes the state values that can be included in the message.

StateState 描述Description
00 出现错误号 8930。Error number 8930 was raised. 这指示导致 DBCC 命令终止的元数据损坏。This indicates a metadata corruption that caused the DBCC command to terminate.
11 出现错误号 8967。Error number 8967 was raised. 存在一个内部 DBCC 错误。There was an internal DBCC error.
22 在紧急模式数据库修复过程中出错。A failure occurred during emergency mode database repair.
33 这指示导致 DBCC 命令终止的元数据损坏。This indicates a metadata corruption that caused the DBCC command to terminate.
44 检测到断言或访问违规。An assert or access violation was detected.
55 出现终止了 DBCC 命令的未知错误。An unknown error occurred that terminated the DBCC command.

错误报告Error Reporting

只要 DBCC CHECKALLOC 检测到损坏错误,就将在 SQL ServerSQL Server LOG 目录中创建微型转储文件 (SQLDUMPnnnn.txt) 。A mini-dump file (SQLDUMPnnnn.txt) is created in the SQL ServerSQL Server LOG directory whenever DBCC CHECKALLOC detects a corruption error. 如果为 SQL ServerSQL Server 实例启用了“功能使用情况数据收集”和“错误报告”功能,该文件将被自动转发给 MicrosoftMicrosoftWhen the Feature Usage data collection and Error Reporting features are enabled for the instance of SQL ServerSQL Server, the file is automatically forwarded to MicrosoftMicrosoft. 收集的数据将用于改进 SQL ServerSQL Server 功能。The collected data is used to improve SQL ServerSQL Server functionality. 转储文件包含 DBCC CHECKALLOC 命令的结果以及其他诊断输出数据。The dump file contains the results of the DBCC CHECKALLOC command and additional diagnostic output. 该文件拥有任意访问控制列表 (DACL)。The file has restricted discretionary access-control lists (DACLs). 只有 SQL ServerSQL Server 服务帐户和 sysadmin 角色的成员有权进行访问。Access is limited to the SQL ServerSQL Server service account and members of the sysadmin role. 默认情况下,sysadmin 角色包含 Windows BUILTIN\Administrators 组和本地管理员组的所有成员。By default, the sysadmin role contains all members of the Windows BUILTIN\Administrators group and the local administrator's group. 如果数据收集进程失败,DBCC 命令不会失败。The DBCC command does not fail if the data collection process fails.

纠正错误Resolving Errors

如果 DBCC CHECKALLOC 报告了任何错误,则建议您通过数据库备份来还原该数据库,而不是运行修复。If DBCC CHECKALLOC reports any errors, we recommend that you restore the database from the database backup instead of running a repair. 如果备份不存在,则运行修复也可纠正报告的错误;但是,纠正这些错误时可能需要删除某些页,进而删除数据。If a backup does not exist, running a repair can correct the reported errors; however, correcting the errors might require some pages, and therefore data, to be deleted. 修复可在用户事务内执行。A repair can be performed in a user transaction. 允许回滚所做的更改。This allows for changes to be rolled back. 如果回滚所做的更改,则数据库仍将包含错误,因此必须通过备份进行还原。If changes are rolled back, the database will still contain errors and must be restored from a backup. 修复完成后,备份该数据库。After the repairs finish, back up the database.

结果集Result Sets

下表说明了 DBCC CHECKALLOC 返回的信息。The following tables describe the information that DBCC CHECKALLOC returns.

Item 描述Description
FirstIAMFirstIAM 仅限内部使用。Internal use only.
RootRoot 仅限内部使用。Internal use only.
DpagesDpages 数据页计数。Data page count.
Pages usedPages used 分配的页。Allocated pages.
Dedicated extentsDedicated extents 分配给对象的区数。Extents allocated to the object.

如果使用混合分配页,则可能有未分配区数的页。If mixed allocation pages are used, there might be pages allocated without extents.

DBCC CHECKALLOC 还会报告每条索引和每个文件中分区的分配摘要。DBCC CHECKALLOC also reports an allocation summary for each index and partition in each file. 此摘要说明了数据的分布情况。This summary describes the distribution of the data.

Item 描述Description
Reserved pagesReserved pages 分配给索引的页和已分配区数中未使用的页。Pages allocated to the index and the unused pages in allocated extents.
Used pagesUsed pages 分配给索引和索引正在使用的页。Pages allocated and being used by the index.
Partition IDPartition ID 仅限内部使用。Internal use only.
Alloc unit IDAlloc unit ID 仅限内部使用。Internal use only.
行内数据In-row data 页包含索引或堆数据。Pages contain index or heap data.
LOB 数据LOB data 页包含 varchar(max)、nvarchar(max)、text、ntext,xml 和 image 数据 。Pages contain varchar(max), nvarchar(max), varbinary(max), text, ntext, xml, and image data.
行溢出数据Row-overflow data 页包含已推送到行外的可变长度列数据。Pages contain variable-length column data that has been pushed off-row.

DBCC CHECKALLOC 将返回以下结果集(值可能有所不同),指定了 ESTIMATEONLY 或 NO_INFOMSGS 时除外。DBCC CHECKALLOC returns the following result set (values may vary), except when ESTIMATEONLY or NO_INFOMSGS is specified.

DBCC results for 'master'.  
***************************************************************  
Table sysobjects                Object ID 1.  
Index ID 1         FirstIAM (1:11)   Root (1:12)    Dpages 22.  
    Index ID 1. 24 pages used in 5 dedicated extents.  
Index ID 2         FirstIAM (1:1368)   Root (1:1362)    Dpages 10.  
    Index ID 2. 12 pages used in 2 dedicated extents.  
Index ID 3         FirstIAM (1:1392)   Root (1:1408)    Dpages 4.  
    Index ID 3. 6 pages used in 0 dedicated extents.  
Total number of extents is 7.  
***************************************************************  
'...'  
***************************************************************  
Table spt_server_info                Object ID 1938105945.  
Index ID 1         FirstIAM (1:520)   Root (1:508)    Dpages 1.  
    Index ID 1. 3 pages used in 0 dedicated extents.  
Total number of extents is 0.  
***************************************************************  
Processed 52 entries in sysindexes for database ID 1.  
File 1. Number of extents = 210, used pages = 1126, reserved pages = 1280.  
           File 1 (number of mixed extents = 73, mixed pages = 184).  
    Object ID 1, Index ID 0, data extents 5, pages 24, mixed extent pages 9.  
'...'  
    Object ID 1938105945, Index ID 0, data extents 0, pages 3, mixed extent pages 3.  
Total number of extents = 210, used pages = 1126, reserved pages = 1280 in this database.  
       (number of mixed extents = 73, mixed pages = 184) in this database.  
CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'master'.  
DBCC results for 'master'.  
***************************************************************  
Table sys.sysrowsetcolumns                Object ID 4.  
Index ID 1, partition ID 262144, alloc unit ID 262144 (type In-row data). FirstIAM (1:98). Root (1:94). Dpages 7.  
Index ID 1, partition ID 262144, alloc unit ID 262144 (type In-row data). 9 pages used in 1 dedicated extents.  
Index ID 1, partition ID 262144, alloc unit ID 262398 (type Row-overflow data). FirstIAM (0:0). Root (0:0). Dpages 0.  
Index ID 1, partition ID 262144, alloc unit ID 262398 (type Row-overflow data). 0 pages used in 0 dedicated extents.  
Total number of extents is 1.  
...  
***************************************************************  
Processed 201 entries in system catalog for database ID 1.  
File 1. Number of extents = 44, used pages = 300, reserved pages = 345.  
           File 1 (number of mixed extents = 29, mixed pages = 225).  
    Object ID 4, index ID 1, partition ID 262144, alloc unit ID 262144 (type In-row data), data extents 1, pages 9, mixed extent pages 8.  
    Object ID 5, index ID 1, partition ID 327680, alloc unit ID 327680 (type In-row data), data extents 0, pages 2, mixed extent pages 2.  
    Object ID 7, index ID 1, partition ID 458752, alloc unit ID 458752 (type In-row data), data extents 0, pages 5, mixed extent pages 5.  
    Object ID 8, index ID 0, partition ID 524288, alloc unit ID 524288 (type In-row data), data extents 0, pages 2, mixed extent pages 2.  
    Object ID 13, index ID 1, partition ID 851968, alloc unit ID 851968 (type In-row data), data extents 1, pages 9, mixed extent pages 8.  
    Object ID 15, index ID 1, partition ID 983040, alloc unit ID 983040 (type In-row data), data extents 0, pages 2, mixed extent pages 2.  
    Object ID 26, index ID 1, partition ID 281474978414592, alloc unit ID 1703937 (type In-row data), data extents 0, pages 3, mixed extent pages 3.  
    Object ID 27, index ID 1, partition ID 281474978480128, alloc unit ID 1769473 (type In-row data), data extents 0, pages 3, mixed extent pages 3.  
    Object ID 27, index ID 2, partition ID 562949955190784, alloc unit ID 1769474 (type In-row data), index extents 0, pages 3, mixed extent pages 3.  
...  
    Object ID 1179151246, index ID 1, partition ID 72057594038845440, alloc unit ID 13435136 (type In-row data), data extents 2, pages 18, mixed extent pages 8.  
    Object ID 1179151246, index ID 2, partition ID 72057594038910976, alloc unit ID 13566208 (type In-row data), index extents 1, pages 16, mixed extent pages 8.  
    Object ID 1911677858, index ID 0, partition ID 72057594039631872, alloc unit ID 15073536 (type In-row data), data extents 0, pages 2, mixed extent pages 2.  
Total number of extents = 41, used pages = 289, reserved pages = 323 in this database.  
       (number of mixed extents = 27, mixed pages = 211) in this database.  
CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'master'.  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.  

如果指定了 ESTIMATEONLY,则 DBCC CHECKALLOC 将返回以下结果集。When ESTIMATEONLY is specified, DBCC CHECKALLOC returns the following result set.

Estimated TEMPDB space needed for CHECKALLOC (KB)   
-------------------------------------------------   
34  
  
(1 row(s) affected)  
  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.  

权限Permissions

要求具有 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员身份。Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

示例Examples

下面的示例将对当前数据库和 DBCC CHECKALLOC 数据库执行 AdventureWorks2012The following example executes DBCC CHECKALLOC for the current database and for the AdventureWorks2012 database.

-- Check the current database.  
DBCC CHECKALLOC;  
GO  
-- Check the AdventureWorks2012 database.  
DBCC CHECKALLOC (AdventureWorks2012);  
GO  

另请参阅See Also

DBCC (Transact-SQL)DBCC (Transact-SQL)