June 15, 2011 at 10:34 am
I've got a SQL Server 2005 DB that's throwing errors in a DBCC data purity check, but I'm finding it impossible to track down - much less correct - the problem. The column in question (CurrentNetValue) is a NUMERIC(12,2). The specific DBCC output is (one example of 32 total errors):
DBCC results for 'BookParts'.
Msg 2570, Level 16, State 3, Line 1
Page (1:6003), slot 0 in object ID 34099162, index ID 1, partition ID 283709699391488, alloc unit ID 2234722680832 (type "In-row data"). Column "CurrentNetValue" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 1
Page (1:6003), slot 2 in object ID 34099162, index ID 1, partition ID 283709699391488, alloc unit ID 2234722680832 (type "In-row data"). Column "CurrentNetValue" value is out of range for data type "numeric". Update column to a legal value.
Msg 2570, Level 16, State 3, Line 1
I've tried running the following:
SELECT *
FROM BookParts
WHERE CurrentNetValue > 9999999999.99
OR CurrentNetValue < -9999999999.99
And gotten no rows returned.
I've run:
DBCC TRACEON ( 3604 )
DBCC PAGE ( FAS_MilwClients , 1 , 6003 , 3 )
And gotten:
Slot 0 Column 0 Offset 0x4 Length 2
CompanyID = 23
Slot 0 Column 1 Offset 0x6 Length 4
AssetID = 32
.
.
.
Slot 0 Column 34 Offset 0xca Length 9
CurrentNetValue = -0.00
Querying the table with the following:
select currentnetvalue
from bookparts
where companyid = 23
and assetid = 32
Gives me a completely normal-looking result set:
currentnetvalue
---------------------------------------
0.00
1870.48
0.00
282.51
3232.69
282.51
0.00
(7 row(s) affected)
I'm at something of a loss, here. None of those values are out of range for a NUMERIC(12,2) as far as I can see. That said, I'm at best a hack when it comes to DBCC PAGE, so it's possible I'm pulling the wrong information. The only thing that looks at all odd to me is the displayed value of the column in the DBCC PAGE output; it looks like negative 0.00.
Anyone have any ideas, or corrections in how I'm researching the problem? Thanks in advance for any help.
______
Twitter: @Control_Group
June 15, 2011 at 10:43 am
It's probably that -0.00 that's the problem. That's not a valid numeric value. Get the pk value for that row from DBCC Page and try an update (don't filter on the column with the bad data, it's likely to fail), see if that fixes the 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
June 15, 2011 at 12:50 pm
OK, an update - you were entirely correct, it was the -0.00 that was the problem. Just spent the last several hours tracking down each record's key information via its page and offset (the word "painstaking" comes to mind), then ran an update against each affected record, setting the relevant field to 0.00.
This has resolved the DBCC errors; the DB checks clean - so now I can run it in production. 🙂
Thanks very much for the input!
______
Twitter: @Control_Group
June 15, 2011 at 1:48 pm
That's ridiculous :hehe:
How does one get the engine to store -0.00 ?
I tried:
USE test
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.neg_zero')
AND type IN (N'U') )
DROP TABLE dbo.neg_zero ;
GO
CREATE TABLE dbo.neg_zero (num NUMERIC(12, 2)) ;
INSERT INTO neg_zero
VALUES (-0.00) ;
SELECT *
FROM dbo.neg_zero ;
DBCC CHECKTABLE ('dbo.neg_zero') WITH DATA_PURITY ;
But SQL Server was up to the task:
(1 row(s) affected)
num
---------------------------------------
0.00
(1 row(s) affected)
DBCC results for 'neg_zero'.
There are 1 rows in 1 pages for object "neg_zero".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 15, 2011 at 2:10 pm
opc.three (6/15/2011)
That's ridiculous :hehe:How does one get the engine to store -0.00 ?
I tried:
USE testGO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.neg_zero')
AND type IN (N'U') )
DROP TABLE dbo.neg_zero ;
GO
CREATE TABLE dbo.neg_zero (num NUMERIC(12, 2)) ;
INSERT INTO neg_zero
VALUES (-0.00) ;
SELECT *
FROM dbo.neg_zero ;
DBCC CHECKTABLE ('dbo.neg_zero') WITH DATA_PURITY ;
But SQL Server was up to the task:
(1 row(s) affected)
num
---------------------------------------
0.00
(1 row(s) affected)
DBCC results for 'neg_zero'.
There are 1 rows in 1 pages for object "neg_zero".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
It's a SQL2000 flaw that was only detectable with dbcc data purity check in SQL2005.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 15, 2011 at 2:15 pm
This usually happens with upgrades from 2000 into a newer version. Correcting it is just setting the fields/rows to a valid value
June 15, 2011 at 2:17 pm
Phew..happy it got that cleaned...thanks for the info gents.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 3:26 am
opc.three (6/15/2011)
That's ridiculous :hehe:How does one get the engine to store -0.00 ?
Well there's a reason it's considered a data-purity form of corruption....
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
June 16, 2011 at 9:44 am
GilaMonster (6/16/2011)
opc.three (6/15/2011)
That's ridiculous :hehe:How does one get the engine to store -0.00 ?
Well there's a reason it's considered a data-purity form of corruption....
OK, well I feel better. At the least it appears as though the T-SQL front door was/is secure.
From the KB article:
Invalid or out-of-range data may have been stored in the SQL Server database in earlier versions for the following reasons:
> Invalid data was present in the source while using bulk insert methods, such as the bcp utility.
> Invalid data was passed through RPC event calls made to SQL Server.
> Other potential causes of physical data corruption left the column value in an invalid state.
I would have suggested the following syntax:
DBCC CHECKTABLE ('dbo.neg_zero') WITH CHECK_FOR_SERVICE_ELEVATOR_DATA ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply