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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

檢查組成資料表或索引檢視表的所有頁面和結構的完整性。Checks the integrity of all the pages and structures that make up the table or indexed view.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

DBCC CHECKTABLE     
(    
    table_name | view_name    
    [ , { NOINDEX | index_id }    
     |, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }     
    ]     
)    
    [ WITH     
        { [ ALL_ERRORMSGS ]    
          [ , EXTENDED_LOGICAL_CHECKS ]     
          [ , NO_INFOMSGS ]    
          [ , TABLOCK ]     
          [ , ESTIMATEONLY ]     
          [ , { PHYSICAL_ONLY | DATA_PURITY } ]     
          [ , MAXDOP = number_of_processors ]    
        }    
    ]    

引數Arguments

table_name | view_nametable_name | view_name
這是要執行完整性檢查的資料表或索引檢視。Is the table or indexed view for which to run integrity checks. 資料表或檢視表名稱必須符合識別碼的規則。Table or view names must comply with the rules for identifiers.

NOINDEXNOINDEX
指定不應該執行使用者資料表之非叢集索引的大量檢查。Specifies that intensive checks of nonclustered indexes for user tables should not be performed. 這會減少整體的執行時間。This decreases the overall execution time. NOINDEX 不會影響系統資料表,因為所有系統資料表索引一律會執行完整性檢查。NOINDEX does not affect system tables because the integrity checks are always performed on all system table indexes.

index_idindex_id
這是要執行完整性檢查的索引識別 (ID) 碼。Is the index identification (ID) number for which to run integrity checks. 如果指定 index_id,DBCC CHECKTABLE 只會執行那個索引及堆積或叢集索引上執行完整性檢查。If index_id is specified, DBCC CHECKTABLE runs integrity checks only on that index, together with the heap or clustered index.

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILDREPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
指定 DBCC CHECKTABLE 修復找到的錯誤。Specifies that DBCC CHECKTABLE repair the found errors. 若要使用修復選項,資料庫必須在單一使用者模式中。To use a repair option, the database must be in single-user mode.

REPAIR_ALLOW_DATA_LOSSREPAIR_ALLOW_DATA_LOSS
嘗試修復所有報告的錯誤。Tries to repair all reported errors. 這些修復可能會造成某些資料的遺失。These repairs can cause some data loss.

REPAIR_FASTREPAIR_FAST
保留語法的目的,只是為了與舊版相容。Syntax is maintained for backward compatibility only. 不會執行任何修復動作。No repair actions are performed.

REPAIR_REBUILDREPAIR_REBUILD
執行不可能造成資料遺失的修復,Performs repairs that have no possibility of data loss. 這可包括快速修復 (例如,修復非叢集索引中遺失的資料列) 以及更耗時的修復 (例如,重建索引)。This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.
此引數不會修復涉及 FILESTREAM 資料的錯誤。This argument does not repair errors involving FILESTREAM data.

注意

最好不要使用這些 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 CHECKCONSTRAINTSIf the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. 如果您必須使用 REPAIR,請執行不含修復選項的 DBCC CHECKTABLE 來尋找要使用的修復層級。If you must use REPAIR, run DBCC CHECKTABLE without a repair option to find the repair level to use. 如果您要使用 REPAIR_ALLOW_DATA_LOSS 層級,建議您在搭配這個選項執行 DBCC CHECKTABLE 之前先備份資料庫。If you are going to use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKTABLE with this option.

ALL_ERRORMSGSALL_ERRORMSGS
顯示沒有限制的錯誤數目。Displays an unlimited number of errors. 系統預設會顯示所有錯誤訊息。All error messages are displayed by default. 指定或省略這個選項沒有任何作用。Specifying or omitting this option has no effect.

EXTENDED_LOGICAL_CHECKSEXTENDED_LOGICAL_CHECKS
如果相容性層級為 100 (SQL Server 2008SQL Server 2008) 或更高,則會針對索引檢視表、XML 索引和空間索引 (如果有的話) 執行邏輯一致性檢查。If the compatibility level is 100 ( SQL Server 2008SQL Server 2008) or higher, performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.
如需詳細資訊,請參閱本主題稍後備註一節中的<對索引執行邏輯一致性檢查>。For more information, see Performing Logical Consistency Checks on Indexes in the Remarks section later in this topic.

NO_INFOMSGSNO_INFOMSGS
隱藏所有參考訊息。Suppresses all informational messages.

TABLOCKTABLOCK
使 DBCC CHECKTABLE 取得共用資料表鎖定,而不使用內部資料庫快照集。Causes DBCC CHECKTABLE to obtain a shared table lock instead of using an internal database snapshot. TABLOCK 會使 DBCC CHECKTABLE 在負荷很重的情況下,仍能在資料表上執行得比較快,但當 DBCC CHECKTABLE 在執行中,它會降低資料表上所能使用的並行性。TABLOCK will cause DBCC CHECKTABLE to run faster on a table under heavy load, but decreases the concurrency available on the table while DBCC CHECKTABLE is running.

ESTIMATEONLYESTIMATEONLY
顯示使用所有其他指定選項來執行 DBCC CHECKTABLE 所需的 tempdb 估計空間量。Displays the estimated amount of tempdb space needed to run DBCC CHECKTABLE with all the other specified options.

PHYSICAL_ONLYPHYSICAL_ONLY
將檢查限制於頁面實體結構、記錄標頭和 B 型樹狀目錄之實體結構的完整性。Limits the checking to the integrity of the physical structure of the page, record headers and the physical structure of B-trees. 這個項目是專為了提供資料表實體一致性的小型負擔檢查而設計的,這項檢查也能夠偵測到損毀的頁面以及可能危及資料的一般硬體失敗。Designed to provide a small overhead check of the physical consistency of the table, this check can also detect torn pages, and common hardware failures that can compromise data. 完整執行 DBCC CHECKTABLE 所需要的時間可能比舊版多許多。A full run of DBCC CHECKTABLE may take considerably longer than in earlier versions. 這個行為的原因如下:This behavior occurs because of the following reasons:

  • 邏輯檢查更完整。The logical checks are more comprehensive.
  • 部分要檢查的基礎結構更複雜。Some of the underlying structures to be checked are more complex.
  • 導入了許多新的檢查,以包含新的功能。Many new checks have been introduced to include the new features.

因此,使用 PHYSICAL_ONLY 選項可能使大型資料表 DBCC CHECKTABLE 的執行階段縮短許多,因此,建議您在實際系統上經常使用它。Therefore, using the PHYSICAL_ONLY option may cause a much shorter run-time for DBCC CHECKTABLE on large tables and is therefore recommended for frequent use on production systems. 我們仍建議您定期完整執行 DBCC CHECKTABLE。We still recommend that a full run of DBCC CHECKTABLE be performed periodically. 這些執行動作的頻率取決於個別商務和實際執行環境特有的因素。The frequency of these runs depends on factors specific to individual businesses and production environments. PHYSICAL_ONLY 一律隱含 NO_INFOMSGS,不允許使用任何修復選項。PHYSICAL_ONLY always implies NO_INFOMSGS and is not allowed with any one of the repair options.

注意

指定 PHYSICAL_ONLY 會造成 DBCC CHECKTABLE 略過 FILESTREAM 資料的所有檢查。Specifying PHYSICAL_ONLY causes DBCC CHECKTABLE to skip all checks of FILESTREAM data.

DATA_PURITYDATA_PURITY
使 DBCC CHECKTABLE 檢查資料表,找出無效或超出範圍的資料行值。Causes DBCC CHECKTABLE to check the table for column values that are not valid or out-of-range. 例如,DBCC CHECKTABLE 偵測到資料行具有大於或小於 datetime 資料類型可接受範圍的日期和時間值;或者,decimal 或近似數值資料類型資料行具有無效的小數位數或有效位數值。For example, DBCC CHECKTABLE detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.
預設會啟用資料行值的完整性檢查,而不需要 DATA_PURITY 選項。Column-value integrity checks are enabled by default and do not require the DATA_PURITY option. 對於從舊版 SQL ServerSQL Server 升級的資料庫,您可以使用 DBCC CHECKTABLE WITH DATA_PURITY 尋找並更正特定資料表的錯誤,不過,必須先在毫無錯誤的情況下完成對資料庫執行 DBCC CHECKDB WITH DATA_PURITY,否則依預設不對資料表啟用資料行值檢查。For databases upgraded from earlier versions of SQL ServerSQL Server, you can use DBCC CHECKTABLE WITH DATA_PURITY to find and correct errors on a specific table; however, column-value checks on the table are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. 此後,依預設 DBCC CHECKDB 和 DBCC CHECKTABLE 會檢查資料行值的完整性。After this, DBCC CHECKDB and DBCC CHECKTABLE check column-value integrity by default.
這個選項報告的驗證錯誤無法使用 DBCC 修復選項更正。Validation errors reported by this option cannot be fixed by using DBCC repair options. 如需手動更正這些錯誤的相關資訊,請參閱知識庫文章 923247:針對 SQL Server 2005 和更新版本中的 DBCC 錯誤 2570 進行疑難排解For information about manually correcting these errors, see Knowledge Base article 923247: Troubleshooting DBCC error 2570 in SQL Server 2005 and later versions.
如果指定 PHYSICAL_ONLY,則不會執行資料行完整性檢查。If PHYSICAL_ONLY is specified, column-integrity checks are not performed.

MAXDOPMAXDOP
適用於SQL ServerSQL Server (從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 至 SQL Server 2017SQL Server 2017 開始)。Applies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 through SQL Server 2017SQL Server 2017).

覆寫陳述式之 sp_configuremax degree of parallelism 設定選項。Overrides the max degree of parallelism configuration option of sp_configure for the statement. MAXDOP 可能會超過使用 sp_configure 所設定的值。The MAXDOP can exceed the value configured with sp_configure. 如果 MAXDOP 超過使用 Resource Governor 所設定的值,資料庫引擎就會使用 ALTER WORKLOAD GROUP (Transact-SQL) 中所描述的 Resource Governor MAXDOP 值。If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). 當您使用 MAXDOP 查詢提示時,適用所有搭配 max degree of parallelism 組態選項使用的語意規則。All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. 如需詳細資訊,請參閱 設定 max degree of parallelism 伺服器組態選項For more information, see Configure the max degree of parallelism Server Configuration Option.

注意

如果 MAXDOP 設定為零,則伺服器會選擇平行處理原則的最大程度。If MAXDOP is set to zero then the server chooses the max degree of parallelism.

RemarksRemarks

注意

若要在資料庫的每份資料表上執行 DBCC CHECKTABLE,請使用 DBCC CHECKDBTo perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

DBCC CHECKTABLE 會確認指定資料表的下列項目:For the specified table, DBCC CHECKTABLE checks for the following:

  • 已正確連結索引、in-row、LOB 和資料列溢位資料頁面。Index, in-row, LOB, and row-overflow data pages are correctly linked.
  • 索引符合正確的排序次序。Indexes are in their correct sort order.
  • 指標一致。Pointers are consistent.
  • 每個頁面中的資料都是合理的,其中包括計算資料行。The data on each page is reasonable, included computed columns.
  • 頁面位移合理。Page offsets are reasonable.
  • 基底資料表的每個資料列在每個非叢集索引中都有相符的資料列,反之亦然。Every row in the base table has a matching row in each nonclustered index, and vice-versa.
  • 資料分割資料表或索引中的每個資料列都在正確的資料分割中。Every row in a partitioned table or index is in the correct partition.
  • 使用 FILESTREAM 將 varbinary(max) 資料儲存在檔案系統內時,檔案系統與資料表之間的連結層級一致性。Link-level consistency between the file system and table when storing varbinary(max) data in the file system using FILESTREAM.

對索引執行邏輯一致性檢查Performing Logical Consistency Checks on Indexes

對索引進行的邏輯一致性檢查會根據資料庫的相容性層級而異,如下所示:Logical consistency checking on indexes varies according to the compatibility level of the database, as follows:

  • 如果相容性層級為 100 (SQL Server 2008SQL Server 2008) 或更高:If the compatibility level is 100 (SQL Server 2008SQL Server 2008) or higher:

    • 除非指定了 NOINDEX,否則 DBCC CHECKTABLE 會針對單一資料表及它的所有非叢集索引進行實體和邏輯一致性檢查。Unless NOINDEX is specified, DBCC CHECKTABLE performs both physical and logical consistency checks on a single table and on all its nonclustered indexes. 但是根據預設,XML 索引、空間索引和索引檢視表只會進行實體一致性檢查。However, on XML indexes, spatial indexes, and indexed views only physical consistency checks are performed by default.
    • 如果指定了 WITH EXTENDED_LOGICAL_CHECKS,將會針對索引檢視表、XML 索引和空間索引 (如果有的話) 執行邏輯檢查。If WITH EXTENDED_LOGICAL_CHECKS is specified, logical checks are performed on an indexed view, XML indexes, and spatial indexes, where present. 根據預設,實體一致性檢查會在邏輯一致性檢查之前執行。By default, physical consistency checks are performed before the logical consistency checks. 如果也指定了 NOINDEX,則只會執行邏輯檢查。If NOINDEX is also specified, only the logical checks are performed.
      這些邏輯一致性檢查會交叉檢查索引物件的內部索引資料表 (其中包含它所參考的使用者資料表)。These logical consistency checks cross check the internal index table of the index object with the user table that it is referencing. 若要尋找外圍的資料列,則會建構內部查詢來執行內部和使用者資料表的完整交集。To find outlying rows, an internal query is constructed to perform a full intersection of the internal and user tables. 執行這個查詢對於效能會有極大的影響,而且無法追蹤其進度。Running this query can have a very high effect on performance, and its progress cannot be tracked. 因此,只有當您懷疑發生了與實體損毀無關的索引問題,或是頁面層級總和檢查碼已經關閉,而且您懷疑發生了資料行層級的硬體損毀時,才建議您指定 WITH EXTENDED_LOGICAL_CHECKS。Therefore, we recommend that you specify WITH EXTENDED_LOGICAL_CHECKS only if you suspect index issues that are unrelated to physical corruption, or if page-level checksums have been turned off and you suspect column-level hardware corruption.
    • 如果此索引為已篩選的索引,DBCC CHECKDB 會執行一致性檢查,以確認索引項目可滿足篩選述詞。If the index is a filtered index, DBCC CHECKDB performs consistency checks to verify that the index entries satisfy the filter predicate.
  • SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,預設不會在保存的計算資料行、UDT 資料行和篩選的索引上執行額外檢查,以避免需耗費大量資源的運算式評估作業。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), additional checks on persisted computed columns, UDT columns, and filtered indexes will not run by default to avoid the expensive expression evaluations. 這項變更可大幅減少對包含這些物件的資料庫執行 CHECKDB 的持續時間。This change greatly reduces the duration of CHECKDB against databases containing these objects. 不過,系統一律會完成這些物件的實體一致性檢查。However, the physical consistency checks of these objects is always completed. 只有在指定 EXTENDED_LOGICAL_CHECKS 選項時,才會執行運算式評估,以及執行 EXTENDED_LOGICAL_CHECKS 選項中已存在的邏輯性檢查 (索引檢視表、XML 索引及空間索引)。Only when EXTENDED_LOGICAL_CHECKS option is specified will the expression evaluations be performed in addition to already present logical checks (indexed view, XML indexes, and spatial indexes) as part of the EXTENDED_LOGICAL_CHECKS option.

  • 如果相容性層級為 90 (SQL Server 2005 (9.x)SQL Server 2005 (9.x)) 以下,則除非指定了 NOINDEX,否則 DBCC CHECKTABLE 會針對單一資料表或索引檢視表及其所有非叢集索引和 XML 索引進行實體和邏輯一致性檢查。If the compatibility level is 90 (SQL Server 2005 (9.x)SQL Server 2005 (9.x)) or less, unless NOINDEX is specified, DBCC CHECKTABLE performs both physical and logical consistency checks on a single table or indexed view and on all its nonclustered and XML indexes. 不支援空間索引。Spatial indexes are not supported.

了解資料庫的相容性層級 To learn the compatibility level of a database
檢視或變更資料庫的相容性層級View or Change the Compatibility Level of a Database

內部資料庫快照集Internal Database Snapshot

DBCC CHECKTABLE 會利用內部資料庫快照集來提供執行這些檢查所需具備的交易一致性。DBCC CHECKTABLE uses an internal database snapshot to provide the transactional consistency that it must have to perform these checks. 如需詳細資訊,請參閱檢視資料庫快照集的疏鬆檔案大小 (Transact-SQL)DBCC (Transact-SQL) 中的<DBCC 內部資料庫快照集使用方式>一節。For more information, see View the Size of the Sparse File of a Database Snapshot (Transact-SQL) and the "DBCC Internal Database Snapshot Usage" section in DBCC (Transact-SQL). 如果無法建立快照集,或指定了 TABLOCK,DBCC CHECKTABLE 會取得共用資料表鎖定來取得必要的一致性。If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKTABLE acquires a shared table lock to obtain the required consistency.

注意

如果是針對 tempdb 執行 DBCC CHECKTABLE,它必須取得共用資料表鎖定。If DBCC CHECKTABLE is run against tempdb, it must acquire a shared table lock. 這是因為基於效能的考量,tempdb 並無法使用資料庫快照集。This is because, for performance reasons, database snapshots are not available on tempdb. 這表示無法取得必要的交易一致性。This means that the required transactional consistency cannot be obtained.

檢查及修復 FILESTREAM 資料Checking and Repairing FILESTREAM Data

針對資料庫和資料表啟用 FILESTREAM 時,您可以選擇將 varbinary(max) 二進位大型物件 (BLOB) 儲存在檔案系統中。When FILESTREAM is enabled for a database and table, you can optionally store varbinary(max) binary large objects (BLOBs) in the file system. 當您針對將 BLOB 儲存於檔案系統中的資料表使用 DBCC CHECKTABLE 時,DBCC 會檢查檔案系統與資料庫之間的連結層級一致性。When using DBCC CHECKTABLE on a table that stores BLOBs in the file system, DBCC checks link-level consistency between the file system and database. 例如,如果資料表包含使用 FILESTREAM 屬性的 varbinary(max) 資料行,DBCC CHECKTABLE 將會檢查檔案系統目錄和檔案與資料表資料列、資料行和資料行值之間是否有一對一的對應。For example, if a table contains a varbinary(max) column that uses the FILESTREAM attribute, DBCC CHECKTABLE will check that there is a one-to-one mapping between file system directories and files and table rows, columns, and column values. 如果您指定 REPAIR_ALLOW_DATA_LOSS 選項,則 DBCC CHECKTABLE 可以修復損毀。DBCC CHECKTABLE can repair corruption if you specify the REPAIR_ALLOW_DATA_LOSS option. 若要修復 FILESTREAM 損毀,DBCC 將會刪除遺失檔案系統資料的任何資料表資料列,而且將會刪除未對應到資料表資料列、資料行或資料行值的任何檔案和目錄。To repair FILESTREAM corruption, DBCC will delete any table rows that are missing file system data and will delete any directories and files that do not map to a table row, column, or column value.

平行檢查物件Checking Objects in Parallel

依預設,DBCC CHECKTABLE 會執行物件的平行檢查。By default, DBCC CHECKTABLE performs parallel checking of objects. 查詢處理器會自動判斷平行處理原則的程度。The degree of parallelism is automatically determined by the query processor. 最大平行處理原則程度的設定方式與平行查詢相同。The maximum degree of parallelism is configured in the same manner as that of parallel queries. 若要限制 DBCC 檢查所能使用的最大處理器數目,請使用 sp_configureTo restrict the maximum number of processors available for DBCC checking, use sp_configure. 如需詳細資訊,請參閱 設定 max degree of parallelism 伺服器組態選項For more information, see Configure the max degree of parallelism Server Configuration Option. 您可以利用追蹤旗標 2528 來停用平行檢查。Parallel checking can be disabled by using trace flag 2528. 如需詳細資訊,請參閱追蹤旗標 (Transact-SQL)For more information, see Trace Flags (Transact-SQL).

注意

在 DBCC CHECKTABLE 作業期間,儲存在按位元組排序之使用者定義型別資料行的位元組,必須等於使用者定義型別值的計算序列化。During a DBCC CHECKTABLE operation, the bytes that are stored in a byte-ordered user-defined type column must be equal to the computed serialization of the user-defined type value. 如果不是這樣,DBCC CHECKTABLE 常式將會報告一致性錯誤。If this is not true, the DBCC CHECKTABLE routine will report a consistency error.

了解 DBCC 錯誤訊息Understanding DBCC Error Messages

DBCC CHECKTABLE 命令執行完成之後,SQL ServerSQL Server 錯誤記錄檔中會寫入一則訊息。After the DBCC CHECKTABLE 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 DescriptionDescription
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 CHECKTABLE 偵測到損毀錯誤時,都會在 SQL ServerSQL Server LOG 目錄中建立小型傾印檔案 (SQLDUMP*nnnn*.txt)。A mini-dump file (SQLDUMP*nnnn*.txt) is created in the SQL ServerSQL Server LOG directory whenever DBCC CHECKTABLE 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 CHECKTABLE 命令的結果以及其他診斷輸出。The dump file contains the results of the DBCC CHECKTABLE 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 CHECKTABLE 報告任何錯誤,建議您從資料庫備份還原資料庫,而不要使用下列其中一個 REPAIR 選項來執行 REPAIR。If DBCC CHECKTABLE reports any errors, we recommend restoring the database from the database backup instead of running REPAIR with one of the REPAIR options. 如果沒有任何備份,執行 REPAIR 可以更正所報告的錯誤。If no backup exists, running REPAIR can correct the errors that are reported. 要使用的 REPAIR 選項會指定在報告的錯誤清單尾端。The REPAIR option to use is specified at the end of the list of reported errors. 不過,利用 REPAIR_ALLOW_DATA_LOSS 選項來更正錯誤,可能需要刪除某些頁面,因而也需要刪除某些資料。However, that correcting the errors by using the REPAIR_ALLOW_DATA_LOSS option might require that some pages, and therefore data, be deleted.
您可以在使用者交易之下執行修復,讓使用者回復已進行的變更。The repair can be performed under a user transaction to allow the user to roll back the changes that have been made. 如果回復修復,資料庫仍會包含錯誤,且必須從備份中還原。If repairs are rolled back, the database will still contain errors and must be restored from a backup. 完成所有修復之後,請備份資料庫。After you have completed all repairs, back up the database.

結果集Result Sets

DBCC CHECKTABLE 會傳回下列結果集。DBCC CHECKTABLE returns the following result set. 如果您只指定了資料表名稱或任何選項,就會傳回相同的結果集。The same result set is returned if you specify only the table name or any of the options.

DBCC results for 'HumanResources.Employee'.    
There are 288 rows in 13 pages for object 'Employee'.    
DBCC execution completed. If DBCC printed error messages, contact your system administrator.    

如果指定了 ESTIMATEONLY 選項,DBCC CHECKTABLE 會傳回下列結果集:DBCC CHECKTABLE returns the following result set if the ESTIMATEONLY option is specified:

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

[權限]Permissions

使用者必須擁有資料表,或是 系統管理員 (sysadmin) 固定伺服器角色、db_owner 固定資料庫角色,或 db_ddladmin 固定資料庫角色的成員。User must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

範例Examples

A.A. 檢查特定資料表Checking a specific table

下列範例會檢查 AdventureWorks2012AdventureWorks2012 資料庫中 HumanResources.Employee 資料表的資料頁完整性。The following example checks the data page integrity of the HumanResources.Employee table in the AdventureWorks2012AdventureWorks2012 database.

DBCC CHECKTABLE ('HumanResources.Employee');    
GO    

B.B. 執行資料表的低負擔檢查Performing a low-overhead check of the table

下列範例會執行 AdventureWorks2012AdventureWorks2012 資料庫中 Employee 資料表的低額外負荷檢查。The following example performs a low overhead check of the Employee table in the AdventureWorks2012AdventureWorks2012 database.

DBCC CHECKTABLE ('HumanResources.Employee') WITH PHYSICAL_ONLY;    
GO    

C.C. 檢查特定索引Checking a specific index

下列範例會檢查存取 sys.indexes 所取得的特定索引。The following example checks a specific index, obtained by accessing sys.indexes.

DECLARE @indid int;    
SET @indid = (SELECT index_id     
              FROM sys.indexes    
              WHERE object_id = OBJECT_ID('Production.Product')    
                    AND name = 'AK_Product_Name');    
DBCC CHECKTABLE ('Production.Product',@indid);    

另請參閱See Also

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