Dealing with Invalid found by DBCC CHECKDB DATA_PURITY

Question

Dealing with Invalid found by DBCC CHECKDB DATA_PURITY

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

It's not a negative attitude. And it's not bullying. I'm fed up of people posting 'advice' that is unhelpful.

But I'm done with this thread now.

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

Paul White SSC Guru Points: 150467 More actions · Answer 2

djrgreene (3/12/2010)
...The data type of many of the identified columns was numeric(16,2), and we have been attempting to follow the directions for locating invalid rows with T-SQL as detailed in http://support.microsoft.com/default.aspx/kb/923247?p=1. So for a numeric(16,2), we used the following T-SQL statement:
select * FROM OPPORTUNITY_FACT
where RECURRING_REVENUE_AMOUNT > 99999999999999.99 -- for numeric(16,2)
or RECURRING_REVENUE_AMOUNT < 99999999999999.99
This, however does not return any rows. We can however, locate the row and the offending column with the DBCC PAGE command, but in order to correct the values, we need to be able to reference the rows in T-SQL. In addition to the query above, we have tried locating rows with invalid columns in them with a where clause referencing other (valid) fields (and values) in the row. No luck.

That'll teach me to read the question properly. OK so have you made the modification suggested by Jack:

SELECT * FROM OPPORTUNITY_FACT

WHERE RECURRING_REVENUE_AMOUNT > 99999999999999.99 -- for numeric(16,2)

OR RECURRING_REVENUE_AMOUNT < -99999999999999.99

If so, please post a short except from DBCC PAGE showing a row you have identified as having an invalid value. Use print option 3 of DBCC PAGE.

It should be straightforward to construct the necessary UPDATE query - but please also identify which column or columns form the primary key.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

I'm guessing that the OP did use the correct query because he said that it returned 0 rows. Otherwise, the query shown would return all rows except where they were exactly 99999999999999.99 (which I'm guessing is none).

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

djrgreene SSC Veteran Points: 204 More actions · Answer 4

Posted in original message.

No we cannot drop and re-create the tables.

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Which column(s) are the primary key of this table?

This should be easy enough to fix with some straightforward updates, but need the pk (or unique column) to identify the row. That said, there's a lot of invalid rows, so this is going to take time to fix. Not difficult, but time consuming.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass