在检查数据库是否出现一致性问题时,出现了以下问题:
DBCC CHECKDB(MyDB)
DBCC CHECKTABLE (MyTable)
/*
MyTable的 DBCC 结果。
消息 2570,级别 16,状态 3,第 1 行
页 (1:20239),槽 6 位于对象 ID 1371151930,索引 ID 1,分区 ID 72057594110345216,分配单元 ID 72057594130530304 (类型为"In-row data")中。列 "NAME" 的值超出了数据类型"varchar"的范围。请将该列更新为合法的值。
消息 2570,级别 16,状态 3,第 1 行
页 (1:20241),槽 13 位于对象 ID 1371151930,索引 ID 1,分区 ID 72057594110345216,分配单元 ID 72057594130530304 (类型为"In-row data")中。列 "NAME" 的值超出了数据类型"varchar"的范围。请将该列更新为合法的值。
消息 2570,级别 16,状态 3,第 1 行
页 (1:20241),槽 20 位于对象 ID 1371151930,索引 ID 1,分区 ID 72057594110345216,分配单元 ID 72057594130530304 (类型为"In-row data")中。列 "NAME" 的值超出了数据类型"varchar"的范围。请将该列更新为合法的值。
消息 2570,级别 16,状态 3,第 1 行
页 (1:20241),槽 27 位于对象 ID 1371151930,索引 ID 1,分区 ID 72057594110345216,分配单元 ID 72057594130530304 (类型为"In-row data")中。列 "NAME" 的值超出了数据类型"varchar"的范围。请将该列更新为合法的值。
消息 2570,级别 16,状态 3,第 1 行
页 (1:20730),槽 19 位于对象 ID 1371151930,索引 ID 1,分区 ID 72057594110345216,分配单元 ID 72057594130530304 (类型为"In-row data")中。列 "NAME" 的值超出了数据类型"varchar"的范围。请将该列更新为合法的值。
消息 2570,级别 16,状态 3,第 1 行
页 (1:20730),槽 20 位于对象 ID 1371151930,索引 ID 1,分区 ID 72057594110345216,分配单元 ID 72057594130530304 (类型为"In-row data")中。列 "NAME" 的值超出了数据类型"varchar"的范围。请将该列更新为合法的值。
消息 2570,级别 16,状态 3,第 1 行
页 (1:20730),槽 27 位于对象 ID 1371151930,索引 ID 1,分区 ID 72057594110345216,分配单元 ID 72057594130530304 (类型为"In-row data")中。列 "NAME" 的值超出了数据类型"varchar"的范围。请将该列更新为合法的值。
消息 2570,级别 16,状态 3,第 1 行
页 (1:20730),槽 28 位于对象 ID 1371151930,索引 ID 1,分区 ID 72057594110345216,分配单元 ID 72057594130530304 (类型为"In-row data")中。列 "NAME" 的值超出了数据类型"varchar"的范围。请将该列更新为合法的值。
消息 2570,级别 16,状态 3,第 1 行
页 (1:20731),槽 26 位于对象 ID 1371151930,索引 ID 1,分区 ID 72057594110345216,分配单元 ID 72057594130530304 (类型为"In-row data")中。列 "NAME" 的值超出了数据类型"varchar"的范围。请将该列更新为合法的值。
消息 2570,级别 16,状态 3,第 1 行
页 (1:20755),槽 17 位于对象 ID 1371151930,索引 ID 1,分区 ID 72057594110345216,分配单元 ID 72057594130530304 (类型为"In-row data")中。列 "NAME" 的值超出了数据类型"varchar"的范围。请将该列更新为合法的值。
消息 2570,级别 16,状态 3,第 1 行
页 (1:20861),槽 15 位于对象 ID 1371151930,索引 ID 1,分区 ID 72057594110345216,分配单元 ID 72057594130530304 (类型为"In-row data")中。列 "NAME" 的值超出了数据类型"varchar"的范围。请将该列更新为合法的值。
对象 'MyTable' 的 771 页中有 21343 行。
CHECKDB 在表 'MyTable' (对象 ID 1371151930)中发现 0 个分配错误和 11 个一致性错误。
*/
消息 2570,级别 16,状态 3,第 1 行
页 (1:20239),槽 6 位于对象 ID 1371151930,索引 ID 1,分区 ID 72057594110345216,分配单元 ID 72057594130530304 (类型为"In-row data")中。列 "NAME" 的值超出了数据类型"varchar"的范围。请将该列更新为合法的值。
当前 SQL Server 实例版本为 Microsoft SQL Server 2008 R2 (RTM) ,而数据库兼容级别为 “SQL Server 2005(90)”,数据库是从旧的实例迁移过来的,但不一定是迁移出现了问题,也可能是在迁移前或迁移后操作数据引起的。
问题的原因是: 在早期版本中,无效或超出范围的数据可能存储在SQL Server数据库中。
这种情况数据页并没有损坏,还是可以查看表里面的所以数据,只是字段值超出了数据类型的存储范围/规范。
解决方法:
查看字段类型,发现字段“NAME” 类型为 VARCHAR(20) 。现在看看那些超出范围的数据是什么样子。
查看其中数据页类型:
DBCC TRACEON(3604,1)
DBCC PAGE ([MyDB] ,1,20239,3 )
……………………
ID = 5351
Slot 0 Column 2 Offset 0xc2 Length 6 Length (physical) 6
……………………
因为ID 的聚集索引,查询找出了该数据页的最大和最小ID,查询表记录看看,同时转换为该字段类型进行修正。
SELECT NAME,convert(varchar(20),NAME) FROM MyTable WITH(NOLOCK) WHERE ID BETWEEN 5351 AND 5379
可以看到,部分字段值出现了问号(未知字符),转换类型修正后正常,出现这种情况可能是插入的时候 Unicode 转换成了一般的varchar类型。出现如题的错误,也有可能是值为如 【-0.00】存储在正数类型的字段中,即出现了不合法的值。
因此,如上案例解决方法为更改正确的值:
UPDATE A SET NAME=convert(varchar(20),NAME) FROM MyTable A WHERE ID BETWEEN 5351 AND 5379
表记录少的话可直接更新表
UPDATE A SET NAME=convert(varchar(20),NAME) FROM MyTable A
更新完成后再检查一遍:
DBCC CHECKTABLE (MyTable) WITH DATA_PURITY
--或者
DBCC CHECKDB(MyDB)
修复完成,没有错误了!!
References:
Troubleshooting DBCC error 2570 in SQL Server 2005 and later versions
DBCC CHECKTABLE (Transact-SQL)