September 15, 2011 at 12:16 pm
Hello All,
I have a nice small database that I have recently moved from 2000 to 2008 R2, and as part of our process I have run DBCC CHECKDB with DATA_PURITY to look for problems. On this particular database I have found a few, which shouldn't be a big deal to clean out. However, I am looking for a little better understanding of exactly what I am seeing.
Note that this is a third party app, so nothing I can do about the design!
Here is the DDL for the table (Heap, no indexes, no keys):
CREATE TABLE [dbo].[FORMLAYOUTSPECS](
[TABLENAME] [char](25) NOT NULL,
[FIELDNAME] [char](25) NOT NULL,
[FIELDISKEYFIELD] [numeric](2, 0) NOT NULL,
[FIELDAUTONUMBER] [numeric](3, 0) NULL,
[FIELDTABSTOP] [numeric](2, 0) NULL,
[FIELDTABINDEX] [numeric](10, 0) NULL,
[FIELDTYPE] [char](10) NULL,
[FIELDTAG] [char](25) NULL,
[FIELDLENGTH] [numeric](10, 0) NULL,
[FIELDMASK] [char](25) NULL,
[FIELDSAVEASMASK] [numeric](2, 0) NULL,
[FIELDTOP] [numeric](10, 0) NULL,
[FIELDLEFT] [numeric](10, 0) NULL,
[FIELDWIDTH] [numeric](10, 0) NULL,
[FIELDHEIGHT] [numeric](10, 0) NULL,
[FIELDCOLOR] [varchar](10) NULL,
[FIELDTOOLTIP] [varchar](100) NULL,
[FIELDQUERYTYPE] [varchar](10) NULL,
[FIELDPRIORITY] [numeric](2, 0) NULL,
[FIELDVARIANCE] [numeric](3, 0) NULL,
[FIELDUSED] [numeric](2, 0) NULL,
[FIELDVISIBLE] [numeric](2, 0) NULL,
[FIELDENABLED] [numeric](3, 0) NULL,
[FIELDLOCKED] [numeric](3, 0) NULL,
[FIELDALIGNMENT] [varchar](10) NULL,
[FIELDMULTILINE] [numeric](2, 0) NULL,
[FIELDSCROLLBAR] [numeric](2, 0) NULL,
[FIELDBOLD] [numeric](3, 0) NULL,
[FIELDFONT] [varchar](25) NULL,
[FIELDFONTSIZE] [numeric](3, 0) NULL,
[FIELDLOOKUPTABLENAME] [varchar](25) NULL,
[FIELDLOOKUPCODE] [varchar](25) NULL,
[FIELDLOOKUPDESC] [varchar](100) NULL,
[FIELDDATASOURCE] [varchar](100) NULL,
[FIELDQUERY] [varchar](25) NULL,
[FIELDQUERYMIN] [varchar](25) NULL,
[FIELDQUERYMAX] [varchar](25) NULL,
[FIELDLABEL] [varchar](25) NULL,
[FIELDLABELVISIBLE] [numeric](2, 0) NULL,
[FIELDLABELTOP] [numeric](10, 0) NULL,
[FIELDLABELLEFT] [numeric](10, 0) NULL,
[FIELDLABELWIDTH] [numeric](10, 0) NULL,
[FIELDLABELCOLOR] [numeric](10, 0) NULL,
[FIELDRECORDSOURCE] [varchar](25) NULL,
[FIELDSHOWINGRID] [numeric](3, 0) NULL,
[FIELDROW] [numeric](3, 0) NULL,
[FIELDALTKEY] [numeric](3, 0) NULL,
[FIELDALTKEYPOSITION] [numeric](3, 0) NULL,
[FIELDMUSTENTER] [varchar](10) NULL,
[FIELDBLANKMASKVALUE] [varchar](25) NULL,
[FIELDCOPYNEW] [numeric](2, 0) NULL,
[FIELDLINKTABLE] [varchar](50) NULL,
[FIELDLINKKEY] [varchar](50) NULL,
[FIELDLINKDISPLAY] [varchar](50) NULL,
[FIELDUCASE] [numeric](2, 0) NULL,
[FIELDSTOREASCODE] [varchar](25) NULL,
[FIELDNAMETOSTORE] [varchar](25) NULL
) ON [PRIMARY]
Here is the relevant portion of DBCC CHECKDB with DATA_PURITY:
You'll have to imagine it in red, I can't get the font color working 🙂
Msg 2570, Level 16, State 3, Line 2
Page (1:2064), slot 9 in object ID 706101556, index ID 0, partition ID 46275071574016, alloc unit ID 46275071574016 (type "In-row data"). Column "FIELDTABSTOP" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 2
Page (1:2064), slot 10 in object ID 706101556, index ID 0, partition ID 46275071574016, alloc unit ID 46275071574016 (type "In-row data"). Column "FIELDTABSTOP" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 2
Page (1:2068), slot 1 in object ID 706101556, index ID 0, partition ID 46275071574016, alloc unit ID 46275071574016 (type "In-row data"). Column "FIELDISKEYFIELD" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 2
Page (1:2068), slot 2 in object ID 706101556, index ID 0, partition ID 46275071574016, alloc unit ID 46275071574016 (type "In-row data"). Column "FIELDLABELVISIBLE" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 2
Page (1:2068), slot 4 in object ID 706101556, index ID 0, partition ID 46275071574016, alloc unit ID 46275071574016 (type "In-row data"). Column "FIELDLABELVISIBLE" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 2
Page (1:2068), slot 8 in object ID 706101556, index ID 0, partition ID 46275071574016, alloc unit ID 46275071574016 (type "In-row data"). Column "FIELDISKEYFIELD" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 2
Page (1:2068), slot 9 in object ID 706101556, index ID 0, partition ID 46275071574016, alloc unit ID 46275071574016 (type "In-row data"). Column "FIELDTABSTOP" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 2
Page (1:2068), slot 10 in object ID 706101556, index ID 0, partition ID 46275071574016, alloc unit ID 46275071574016 (type "In-row data"). Column "FIELDTABSTOP" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 2
Page (1:2070), slot 0 in object ID 706101556, index ID 0, partition ID 46275071574016, alloc unit ID 46275071574016 (type "In-row data"). Column "FIELDTABSTOP" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 2
Page (1:2070), slot 2 in object ID 706101556, index ID 0, partition ID 46275071574016, alloc unit ID 46275071574016 (type "In-row data"). Column "FIELDTABSTOP" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 2
Page (1:2070), slot 10 in object ID 706101556, index ID 0, partition ID 46275071574016, alloc unit ID 46275071574016 (type "In-row data"). Column "FIELDTABSTOP" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 2
Page (1:3425), slot 8 in object ID 706101556, index ID 0, partition ID 46275071574016, alloc unit ID 46275071574016 (type "In-row data"). Column "FIELDTABSTOP" value is out of range for data type "numeric". Update column to a legal value.
There are 60 rows in 5 pages for object "FORMLAYOUTSPECS".
Here is the output of DBCC PAGE, and I want to look at the first bad value, in slot 9 on page 2064.
Note that I have omitted slots 0-8.
DBCC TRACEON (3604)
DBCC Page(6,1,2064,1)
/*********************************/
--Header
PAGE: (1:2064)
BUFFER:
BUF @0x00000000E4FB8900
bpage = 0x00000000E4418000 bhash = 0x0000000000000000 bpageno = (1:2064)
bdbid = 6 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 2756 bstat = 0xc00009
blog = 0x21432159 bnext = 0x0000000000000000
PAGE HEADER:
Page @0x00000000E4418000
m_pageId = (1:2064) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 706101556 m_indexId (AllocUnitId.idInd) = 0
Metadata: AllocUnitId = 46275071574016 Metadata: PartitionId = 46275071574016
Metadata: IndexId = 0 Metadata: ObjectId = 706101556 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 309 m_slotCnt = 17
m_freeCnt = 1243 m_freeData = 6915 m_reservedCnt = 0
m_lsn = (7075:2036:2) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 1836289
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x63 MIXED_EXT ALLOCATED 95_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DATA:
--Skipping ahead to slot 9
Slot 9, Offset 0xab1, Length 455, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 455
Memory Dump @0x000000001FF3AAB1
0000000000000000: 30003501 74626c53 4d542020 20202020 †0.5.tblSMT
0000000000000010: 20202020 20202020 20202020 20534d54 † SMT
0000000000000020: 20202020 20202020 20202020 20202020 †
0000000000000030: 20202020 20200100 00000001 00000000 † ..........
0000000000000040: 02010000 00010300 00000000 00004c69 †..............Li
0000000000000050: 73742020 20202020 20202020 20202020 †st
0000000000000060: 20202020 20202020 20202020 20202020 †
0000000000000070: 20010500 00000000 00002020 20202020 † .........
0000000000000080: 20202020 20202020 20202020 20202020 †
0000000000000090: 20202001 00000000 01230000 00000000 † ......#......
00000000000000A0: 0001b004 00000000 00000180 0c000000 †..°.............
00000000000000B0: 00000001 2c010000 00000000 01000000 †....,...........
00000000000000C0: 00010100 00000201 00000002 01000000 †................
00000000000000D0: 01000000 00010100 00000100 00000001 †................
00000000000000E0: 00000000 01000000 00010000 00000201 †................
00000000000000F0: 00000001 23000000 00000000 01200300 †....#........ ..
0000000000000100: 00000000 0001ee02 00000000 00000100 †......î.........
0000000000000110: 00000000 00000001 00000000 01040000 †................
0000000000000120: 00010000 00000100 00000001 00000000 †................
0000000000000130: 02010000 00390000 8001101a 08020015 †.....9..........
0000000000000140: 006b016b 016f0173 0173017f 0183018e †.k.k.o.s.s....
0000000000000150: 018e0199 01990199 019d019d 019e019e †.............
0000000000000160: 01b001b6 01c101c3 01c7014c 6973744c †.°.¶.Á.Ã.Ç.ListL
0000000000000170: 6566744c 5574626c 534d5454 79706543 †eftLUtblSMTTypeC
0000000000000180: 6f646544 65736372 69707469 6f6e4465 †odeDescriptionDe
0000000000000190: 73637269 7074696f 6e547970 65304c55 †scriptionType0LU
00000000000001A0: 74626c53 4d544465 73637269 70746f72 †tblSMTDescriptor
00000000000001B0: 4c554e61 6d654465 73637269 7074696f †LUNameDescriptio
00000000000001C0: 6e2d3143 6f6465††††††††††††††††††††††n-1Code
So, now I have the row data, and I know I can replace the bad col with a proper value, but I really just want to understand... So, just looking at the first bits (bytes!):
0000000000000000: 30003501 74626c53 4d542020 20202020 †0.5.tblSMT
0000000000000010: 20202020 20202020 20202020 20534d54 † SMT
0000000000000020: 20202020 20202020 20202020 20202020 †
0000000000000030: 20202020 20200100 00000001 00000000 † ..........
0000000000000040: 02010000 00010300 00000000 00004c69 †..............Li
0000000000000050: 73742020 20202020 20202020 20202020 †st
0000000000000060: 20202020 20202020 20202020 20202020 †
Assuming I understand correctly, the first four bytes is the row header: 30003501
Then we start the fixed length cols, which is where we will find the field in question, at offset 0x40.
The five bytes found there are : 0201000000
Based on everything I found regarding numeric storage:
- the first byte stores precision, in this case 02, which matches the ddl
- the second byte stores scale, in this case 01, which does not match the ddl
- the third byte I don't know
- the fourth byte has the sign (00 for neg, 01 for pos)
- the fifth byte has the value
Is this an accurate description?
The number returned in queries for this row is -1. But that doesn't look like what is on the page, unless I take the next byte as well, which would be six for this col - that CAN'T be right... 🙂
Is the problem here that the engine sees this defined as numeric(2,1) when in reality it is (2,0)?
If you specify negative with the fourth byte, can you still have zero? Or, does negative require a non-zero number? Perhaps I have missed a byte of overhead?
Can anyone help clear this up for me?
Am I looking at the right bytes for this column? Did I overlook the NULL Bitmap, or am I right that it is at 0x135?
I'll attach a DBCC Page type 3 for more detail for those that want it.
Remember, nothing is down. No outage, I can re-enter the bad data.
And, thanks!
-Dan B
September 15, 2011 at 12:45 pm
Can you post a dump style 3 DBCC PAGE output that renders correctly in notepad - then we can help you.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
September 15, 2011 at 1:26 pm
Damn...
Sorry Paul, should have verified it.
How about this?
-Dan
September 15, 2011 at 2:34 pm
Decimals use a whole byte to indicate whether the number is positive or negative - basically a sign bit taking up a dedicated byte. Next we have 1-4 x 4 bytes, depending on the precision. With a precision of 2 you'll have a total of 5 bytes - one for the sign byte and 4 for the data.
Looking at the value 0x0201000000 I can't get it to make sense. The sign byte should either be 0 or 1 depending on whether it's negative (0) or positive (1). A sign byte value of 2 shouldn't be possible AFAIK. The next 4 bytes indicate the value itself, stored in little endian - having an integer value of 1 in this case.
My guess is CHECKDB doesn't like the sign byte value. I may be completely wrong, and I'm sure Paul will be the first one to correct me in that case 🙂
Mark S. Rasmussen
Blog: improve.dk
Twitter: @improvedk
Internals geek & author of OrcaMDF
September 15, 2011 at 2:49 pm
The bytes that are 2 in your incorrect fields should be 1 or 0 - the sign. Create a table with numeric (2,0) and then insert 1 and -1 into it and compare them.
[Edit after seeing Mark's reply - Mark is correct also.]
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
September 15, 2011 at 3:01 pm
Mark S. Rasmussen (9/15/2011)
Looking at the value 0x0201000000 I can't get it to make sense. The sign byte should either be 0 or 1 depending on whether it's negative (0) or positive (1). A sign byte value of 2 shouldn't be possible AFAIK.
Well it is throwing a data purity error... 🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2011 at 3:05 pm
GilaMonster (9/15/2011)
Mark S. Rasmussen (9/15/2011)
Looking at the value 0x0201000000 I can't get it to make sense. The sign byte should either be 0 or 1 depending on whether it's negative (0) or positive (1). A sign byte value of 2 shouldn't be possible AFAIK.Well it is throwing a data purity error... 🙂
Hehe, true, I could've used better wording there - I'm just trying to imitate DBCC CHECKDB: "Now, what would CHECKDB say if it could speak..." 😉
Mark S. Rasmussen
Blog: improve.dk
Twitter: @improvedk
Internals geek & author of OrcaMDF
September 15, 2011 at 3:09 pm
I can think of all kinds of things I'd like to make it say...
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
September 15, 2011 at 3:12 pm
Paul Randal (9/15/2011)
I can think of all kinds of things I'd like to make it say...
Baaaaaaaaahd corruption encountered?
Mark S. Rasmussen
Blog: improve.dk
Twitter: @improvedk
Internals geek & author of OrcaMDF
September 15, 2011 at 6:53 pm
Ok - i'll take it as a given I should have done a little more legwork on this one. 🙂
It should then come as a surprise to noone that I get 0x0101000000 for 1 and 0x0001000000 for -1.
But, what I am left wondering is, where are the precision and scale defined?
When I defined a numeric(3,0) and a numeric(3,2) and set the former to 100 and the latter to 1.00 they appear to me to both be stored in the row as 0x0164000000.
The only other data I see in the row, apart from the header, is the column count and the null bitmap.
Slot 0 Offset 0xb8 Length 27
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 27
Memory Dump @0x0000000024CDA0B8
0000000000000000: 10001800 01010000 00010a00 00000164 †...............d
0000000000000010: 00000001 64000000 040000†††††††††††††....d......
Slot 0 Column 1 Offset 0x4 Length 5 Length (physical) 5
field1 = 1.
Slot 0 Column 2 Offset 0x9 Length 5 Length (physical) 5
field2 = 1.0
Slot 0 Column 3 Offset 0xe Length 5 Length (physical) 5
field3 = 1.00
Slot 0 Column 4 Offset 0x13 Length 5 Length (physical) 5
field4 = 100.
For this ddl
Create Table testNumeric
(
field1 numeric(2,0) not null,
field2 numeric(2,1) not null,
field3 numeric(3,2) not null,
field4 numeric(3,0) not null
);
Thanks!
-Dan B
September 15, 2011 at 7:23 pm
As further example of my confusion, this:
Create Table testNumeric2
(
field1 numeric (6,0) not null,
field2 numeric (6,1) not null,
field3 numeric (6,2) not null,
field4 numeric (6,3) not null,
field5 numeric (6,4) not null,
field6 numeric (6,5) not null,
field7 numeric (6,6) not null
);
insert into testNumeric2 values(100000,10000.0,1000.00,100.000,10.0000,1.00000,.100000);
Yields this:
Slot 0 Offset 0x60 Length 42
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 42
Memory Dump @0x0000000024CDA060
0000000000000000: 10002700 01a08601 0001a086 010001a0 †..'.. ?... ?...
0000000000000010: 86010001 a0860100 01a08601 0001a086 †?... ?... ?... ?
0000000000000020: 010001a0 86010007 0000†††††††††††††††... ?.....
Slot 0 Column 1 Offset 0x4 Length 5 Length (physical) 5
field1 = 100000.
Slot 0 Column 2 Offset 0x9 Length 5 Length (physical) 5
field2 = 10000.0
Slot 0 Column 3 Offset 0xe Length 5 Length (physical) 5
field3 = 1000.00
Slot 0 Column 4 Offset 0x13 Length 5 Length (physical) 5
field4 = 100.000
Slot 0 Column 5 Offset 0x18 Length 5 Length (physical) 5
field5 = 10.0000
Slot 0 Column 6 Offset 0x1d Length 5 Length (physical) 5
field6 = 1.00000
Slot 0 Column 7 Offset 0x22 Length 5 Length (physical) 5
field7 = 0.100000
Which to me looks like 7 identical values, 0x01a0860100, even though they are clearly not.
I know I am missing something...Hopefully it is not common sense.
-Dan B
September 15, 2011 at 7:32 pm
hah... Record Size = 42
I'll have to dig out my old copy of The Hitchhiker's Guide...:-)
September 16, 2011 at 1:44 am
skrilla99 (9/15/2011)
But, what I am left wondering is, where are the precision and scale defined?
In the system tables. It would be a waste of space to store that it's a numeric (2,0) on every single row, as opposed to defining it in the system tables (the one that underlies sys.columns) once.
It's the variable-length columns (varchar, varbinary, nvarchar) that have their length defined in the row, because that length changes row-to-row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2011 at 1:52 am
skrilla99 (9/15/2011)
But, what I am left wondering is, where are the precision and scale defined?
Just to geek out:
They're stored in the sys.sysrscols base table (accessible only from the DAC, commonly accessed through the sys.system_internals_partition_columns dmv). In there, it's stored in the "ti" field, which is a 4-byte integer value.
To get the precision you'd fetch the second byte from the ti value like so:
(ti & 0xFF00) >> 8
And to get the scale, you'd fetch the third byte:
(ti & 0xFF0000) >> 16
Mark S. Rasmussen
Blog: improve.dk
Twitter: @improvedk
Internals geek & author of OrcaMDF
September 16, 2011 at 5:32 am
Very Cool.
That is exactly what I was looking for.
Mark, Gail and Paul - Thank you very much!
-Dan B
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply