解决checkdb时候的数据纯度错误

解决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.

 

01	use master

02	create database Test1; -- create a new database for testing

03

go

04

use Test1

05

go

06	create table t1(Value numeric(5,1))

07

go

08	insert into t1 values(0)

09	insert into t1 values(0) -- create a table and put 2 zeros

10

go

11	select * from t1

12	select distinct Value from t1

13	--Ensure the data are correct.

14

/*

15

Value

16	---------------------------------------

17

0.0

18

0.0

19

20	(2 row(s) affected)

21

22

Value

23	---------------------------------------

24

0.0

25

26	(1 row(s) affected)

27

28

*/

29

go

30	dbcc checkdb -- no problem

31

go

32	-- Now let's find out where the data located

33	dbcc traceon(3604)

34

go

35	dbcc ind(test1, t1,0) -- page 118 on my machine

36

go

37	dbcc page (test1, 1, 118, 1)

38

go

39

/*

40	Slot 0, Offset 0x60, Length 12, DumpStyle BYTE

41

42	Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 12

43

44	Memory Dump @0x000000001424A060

45

46	0000000000000000:   10000900 01000000 00010000                    ..    .........

47

48	Slot 1, Offset 0x6c, Length 12, DumpStyle BYTE

49

50	Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 12

51

52	Memory Dump @0x000000001424A06C

53

54	0000000000000000:   10000900 01000000 00010000                    ..    .........

55

56

*/

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)

02

go

03	---the data is changed, if I run select, no error returned

04	select  Value from t1

05

/*

06

Value

07	---------------------------------------

08

0.0

09

0.0

10

11	(2 row(s) affected)

12

13

*/

14	---What if I run distinct, will SQL Server identify them as 2 numbers or 1 number/

15	select  distinct Value from t1

16

/*

17

Value

18	---------------------------------------

19

0.0

20

0.0

21

22	(2 row(s) affected)

23

24

*/

25	-- interesting enough, SQL Server thinks they are 2 numbers.

26

go

27	--what if I run a search? SQL thinks -0 is less than zero

28	select Value from t1 where Value = 0

29	select Value from t1 where Value < 0

30

/*

31

Value

32	---------------------------------------

33

0.0

34

35	(1 row(s) affected)

36

37

Value

38	---------------------------------------

39

0.0

40

41	(1 row(s) affected)

42

43

44

*/

45

go

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