解决checkdb时候的数据纯度错误
http://www.sqlnotes.info/2013/04/30/sql-server-0-and-0/
This is a very interesting error happened in one of my clients’ databases. Their database has been running for at least 12 years without a single problem. They run DBCC CheckDB regularly to ensure the database is in healthy state until few days ago their DBA ran DBCC CHECKTABLE with data_purity
and get
1 |
Msg 2570, Level 16, State 3, Line 1 |
2 |
Page (1:118), slot 0 in object ID 245575913, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043432960 (type "In-row data"). Column "Value" value is out of range for data type "numeric". Update column to a legal value. |
Finally, they found this article, , http://support.microsoft.com/kb/923247. — Many things can cause this kind of error. In this blog post, I am going to demo how I repeat and fix this error — for fun.
02 |
create database Test1; |
06 |
create table t1(Value numeric (5,1)) |
08 |
insert into t1 values (0) |
09 |
insert into t1 values (0) |
12 |
select distinct Value from t1 |
37 |
dbcc page (test1, 1, 118, 1) |
40 |
Slot 0, Offset 0x60, Length 12, DumpStyle BYTE |
42 |
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12 |
44 |
Memory Dump @0x000000001424A060 |
46 |
0000000000000000: 10000900 01000000 00010000 .. ......... |
48 |
Slot 1, Offset 0x6c, Length 12, DumpStyle BYTE |
50 |
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12 |
52 |
Memory Dump @0x000000001424A06C |
54 |
0000000000000000: 10000900 01000000 00010000 .. ......... |
Now we have 2 rows on the page 118. each record has 12 bytes. Look at the body above, there are 3 segments of numbers for each records. The segment in the middle contains the numeric number. We notice that it’s 0x01000000 in which it presents the value of the field with the byte next to it. The first byte in this case is presented as sign. Now let’s do some modification to that byte. I am going to change it from 0x01 to 0x00 to see what will happen
01 |
dbcc writepage(test1, 1, 118, 100, 1, 0x00) |
15 |
select distinct Value from t1 |
28 |
select Value from t1 where Value = 0 |
29 |
select Value from t1 where Value < 0 |
DBCC CheckDB will give you the error at the beginning of the article. Using REPAIR_REBUILD option can’t fix the problem. We can’t afford to use with data loss option to fix the data. We come up with the solution to update -0s to +0s.
1 |
update t1 set Value = 0 where ABS (value) = 0 and ABS (value)!= Value |
After the update statement, DBCC CheckDB no longer reports errors. This database was created in the version prior to SQL Server 2005. It was upgraded when a new major version was on the market. According to the article, The data purity check was disabled while upgrading. that’s why DBAs did not figure out this problem in last few years. If you have the database upgraded from earlier version of SQL Server, I would highly recommend you to run DBCC CheckDB with Data_Purity.
http://www.sqlnotes.info
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
2013-10-25 带您理解SQLSERVER是如何执行一个查询的
2013-10-25 SQL Server 2000中的并行处理和执行计划中的位图运算符
2012-10-25 SQLSERVER model数据库