MSSQLSERVER_2570

適用於：SQL Server

詳細資料

說明

包含在指定資料行中的資料行值超出資料行資料類型的可能值範圍。 如果您在資料表資料行中有不正確資料，可能會發生問題，視針對無效資料執行的作業類型而定。 不過，也可能不會出現任何問題，而且在您執行 DBCC CHECKDB 或 DBCC CHECKTABLE 命令之前，將不會探索到不正確資料。

由於資料無效，您可能會注意到某些徵兆（但不限於）：

• 對受影響的資料行執行查詢時，存取違規或其他例外狀況。
• 針對受影響資料行執行的查詢所傳回的結果不正確。
• 針對受影響的資料行建置統計資料時發生錯誤或問題。
• 錯誤訊息，如下所示：

  偵測到 Msg 9100、Level 23、State 2、Line LineNum <> 可能索引損毀。 請執行 DBCC CHECKDB。

DATA_PURITY檢查

當您執行 DBCC CHECKDB 或 DBCC CHECKTABLE 命令時，SQL Server 會在資料庫中每個資料表的每個資料列中執行資料行值的「資料純潔性」驗證。 系統會執行這些檢查，以確保儲存在資料行中的值有效。 也就是說，驗證可確保值不會超出與資料行資料類型相關聯的定義域範圍。 執行的驗證本質取決於資料行的資料類型。 下列非詳盡清單提供一些範例：

並非所有資料類型都會檢查資料行資料的有效性。 只會檢查可能具有超出範圍的預存值。 例如， tinyint 資料類型的有效範圍是 0 到 255，並且儲存在單一位元組中（這只能儲存介於 0 到 255 之間的值），因此檢查值並非必要。

DATA_PURITY問題報告

當您執行 DBCC CHECKDB 已啟用 選項的 或 DBCC CHECKTABLE 命令 DATA_PURITY 時（或資料純潔度檢查會自動執行），且命令所 DBCC 檢查的資料表中存在不正確資料時， DBCC 輸出會包含其他訊息，指出與資料相關的問題。 下列範例錯誤訊息指出資料純潔性問題：

DBCC results for "account_history". 
Msg 2570, Level 16, State 2, Line <LineNum> 
Page (1:1073), slot 33 in object ID <ObjectID>, index ID 0, partition ID <PartitionID>, alloc unit ID <UnitID> (type "In-row data"). Column "account_name" value is out of range for data type "nvarchar". Update column to a legal value. 
 
Msg 2570, Level 16, State 2, Line <LineNum> 
Page (1:1156), slot 120 in object ID <ObjectID>, index ID 0, partition ID <PartitionID>, alloc unit ID <UnitID> (type "In-row data"). Column "account_name" value is out of range for data type "nvarchar". Update column to a legal value.
There are 153137 rows in 1080 pages for object "account_history". 
CHECKDB found 0 allocation errors and 338 consistency errors in table "account_history" (object ID <ObjectID>). 
CHECKDB found 0 allocation errors and 338 consistency errors in database '<DatabaseName>'. 
DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

DBCC results for 'table1'. 
Msg 2570, Level 16, State 3, Line <LineNum> 
Page (1:154), slot 0 in object ID <ObjectID>, index ID 0, partition ID <PartitionID>, alloc unit ID <UnitID> (type "In-row data"). Column "col2" value is out of range for data type "real". Update column to a legal value. 
There are 4 rows in 2 pages for object "table1". 
CHECKDB found 0 allocation errors and 1 consistency errors in table 'table1' (object ID <ObjectID>). 
CHECKDB found 0 allocation errors and 1 consistency errors in database 'realdata'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

DBCC results for 'table2'. 
Msg 2570, Level 16, State 3, Line <LineNum> 
Page (1:155), slot 0 in object ID <ObjectID>, index ID 0, partition ID <PartitionID>, alloc unit ID <UnitID> (type "In-row data"). Column "col2" value is out of range for data type "decimal". Update column to a legal value. 
There are 4 rows in 1 pages for object "table2". 
CHECKDB found 0 allocation errors and 1 consistency errors in table 'table2' (object ID <ObjectID>). 
CHECKDB found 0 allocation errors and 1 consistency errors in database 'realdata'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

DBCC results for 'table3'. 
Msg 2570, Level 16, State 3, Line <LineNum> 
Page (1:157), slot 0 in object ID <ObjectID>, index ID 0, partition ID <PartitionID>, alloc unit ID <UnitID> (type "In-row data"). Column "col2" value is out of range for data type "datetime". Update column to a legal value. 
There are 3 rows in 1 pages for object "table3". 
CHECKDB found 0 allocation errors and 1 consistency errors in table 'table3' (object ID <ObjectID>). 
CHECKDB found 0 allocation errors and 1 consistency errors in database 'realdata'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

For every row that contains an invalid column value, a 2570 error is generated. 

原因

無效或超出範圍的資料可能已儲存在 SQL Server 資料庫中，原因如下：

• 透過遠端程序呼叫 （RPC） 事件，將資料插入 SQL Server 中無效。
• 實體資料損毀的其他可能原因使資料行值無效。

修正資料純潔性問題

無法使用任何 DBCC 修復選項來修復 2570 錯誤。 原因是 DBCC 無法判斷應該使用哪些值來取代不正確資料行值。 因此，資料行值必須手動更新。 若要執行手動更新，您必須找出有問題的資料列。 使用下列其中一種方法來尋找資料列：

• 針對包含無效值的資料表執行查詢，以尋找包含無效值的資料列。
• 使用錯誤 2570 中的資訊來識別具有無效值的資料列。

下列各節會詳細說明這兩種方法，並提供範例來尋找具有無效資料的資料列。

找到正確的資料列之後，必須針對將用來取代現有無效資料的新值做出決策。 必須根據適用于應用程式的值範圍，以及該特定資料列的邏輯意義，非常謹慎地做出此決定。 下列選項可供您選擇：

• 如果您知道它應該的值，請將它設定為該特定值。
• 將它設定為可接受的預設值。
• 將資料行值設定為 NULL 。
• 將資料行值設定為數據行資料類型的最大值或最小值。
• 如果您認為特定資料列沒有資料行的有效值就沒有用處，請完全刪除該資料列。

使用 T-SQL 查詢尋找具有無效值的資料列

您需要執行的查詢類型，以尋找具有無效值的資料列，取決於報告問題的資料行資料類型。 如果您查看 2570 錯誤訊息，您會注意到兩個重要資訊，可協助您解決此問題。 在下列範例中，資料類型 的資料行 account_name 值超出範圍 nvarchar 。 我們可以輕鬆地識別有問題的資料行，以及涉及之資料行的資料類型。 因此，一旦您知道資料類型和涉及的資料行之後，您可以制定查詢來尋找包含該資料行無效值的資料列，並選取識別該資料列所需的資料行（作為 子句中的述詞），以進行任何進一 WHERE 步的更新或刪除。

Unicode 資料類型

SELECT col1, DATALENGTH(account_name) AS Length, account_name  
FROM account_history 
WHERE DATALENGTH(account_name) % 2 != 0

Float 資料類型

將 變更為實際主鍵資料行、 col2 從 2570 錯誤變更 col1 為數據行，以及 table1 從 CHECKDB 輸出變更為資料表，以執行下列程式碼片段。

SELECT col1, col2 FROM table1 
WHERE col2<>0.0 AND (col2 < 2.23E-308 OR col2 > 1.79E+308) AND (col2 < -1.79E+308 OR col2 > -2.23E-308)

實際資料類型

將 變更為實際主鍵資料行、 col2 從 2570 錯誤變更 col1 為數據行，以及 table1 從 CHECKDB 輸出變更為資料表，以執行下列程式碼片段。

SELECT col1, col2 FROM testReal  
WHERE col2<>0.0 AND (col2 < CONVERT(real,1.18E-38) OR col2 > CONVERT(real,3.40E+38)) AND (col2 < CONVERT(real,-3.40E+38) OR col2 > CONVERT(real,-1.18E-38))  
ORDER BY col1; -- checks for real out of range 

十進位和數值資料類型

SELECT col1 FROM table2 
WHERE col2 > 9999999999.99999  
OR col1 < -9999999999.99999

請記住，您必須根據您已定義 decimal 或 numeric 資料行的有效位數和小數位數來調整值。 在上述範例中，資料行定義為 col2 decimal(15,5) 。

Datetime 資料類型

您必須執行兩個不同的查詢，以識別包含資料行值不正確資料 datetime 列。

SELECT col1 FROM table3 
WHERE col2 < '1/1/1753 12:00:00 AM' OR col2 > '12/31/9999 11:59:59 PM' 

SELECT col1 FROM table3 WHERE 
((DATEPART(ms,col2)+ (1000*DATEPART(s,col2)) + (1000*60*DATEPART(mi,col2)) + (1000*60*60*DATEPART(hh,col2)))/(1000*0.00333))  > 25919999

使用實體位置尋找具有無效值的資料列

如果您使用 T-SQL 方法找不到具有無效值 的資料列，您可以使用這個方法 。 在 2570 錯誤訊息中，列印包含無效值之資料列的實體位置。 例如，查看下列訊息：

Page (1:157), slot 0 in object ID <ObjectID>, index ID 0, partition ID <PartitionID>, alloc unit ID <UnitID> (type "In-row data"). Column "col2" value is out of range for data type "datetime". Update column to a legal value. 

在此訊息中，您會注意到 Page (1:157), slot 0 。 這是識別資料列所需的資訊。 FileId是 1 ， PageInFile 是 157 ，而 SlotId 是 0 。

擁有這項資訊之後，您必須執行下列命令：

DBCC TRACEON (3604)
DBCC PAGE (realdata , 1 , 157 , 3)

執行此命令之後，您會注意到包含類似下列格式資訊的輸出：

Slot 0  Offset 0x60 Length 19
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x44D1C060
00000000: 10001000 01000000 ffffffff ffffffff †................
00000010: 0200fc†††††††††††††††††††††††††††††††...
Slot 0 Column 0  Offset 0x4 Length 4  col1 = 1
Slot 0 Column 1  Offset 0x8 Length 8  col2 = Dec 31 1899 19:04PM
Slot 1 Offset 0x73 Length 19
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x44D1C073
00000000: 10001000 02000000 0ba96301 f8970000 †..........c.....
00000010: 0200fc†††††††††††††††††††††††††††††††...
Slot 1 Column 0 Offset 0x4 Length 4 col1 = 2
Slot 1 Column 1 Offset 0x8 Length 8 col2 = Jul 8 2006 9:34PM
Slot 2 Offset 0x86 Length 19
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x44D1C086
00000000: 10001000 03000000 0ba96301 f8970000 †..........c.....
00000010: 0200fc†††††††††††††††††††††††††††††††... 
Slot 2 Column 0 Offset 0x4 Length 4 col1 = 3
Slot 2 Column 1 Offset 0x8 Length 8 col2 = Jul 8 2006 9:34PM

在此輸出中，您可以清楚地看到感興趣的資料列資料行值。 在此情況下，您需要儲存在 slot 0 頁面中的資料列。 從錯誤訊息中，您知道 col2 這是有問題的訊息。 因此，您可以接受 的值 col1Slot 0 ，並將其當做 update 語句的 子句中的 WHERE 述詞，或 delete 語句。

另請參閱

• UPDATE (Transact-SQL)
• 資料類型 (Transact-SQL)
• DBCC (Transact-SQL)
• 呼叫預存程式