Fixing DBCC Check Error 2570

Whilst moving a database from a dying Sql Server 2000 box to a newer Sql Server 2008R2 server I ran a checkdb to ensure everything was kosher with the database (of course, after running DBCC UPDATEUSAGE).  Lo and behold, everything was not okay:

DBCC CHECKDB ([DATABASENAME] ) 
WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY 

Msg 2570, Level 16, State 3, Line 1

Page (1:131693), slot 60 in object ID 1365579903, index ID 1, partition ID 370969621233664, alloc unit ID 89494644523008 (type “In-row data”). Column “Example_DATE” value is out of range for data type “datetime”.  Update column to a legal value.

What does this mean exactly?  Well, it’s saying that (in my case), on page 131693 of file 1 a datetime value is not a valid value for the datetime type.  What is the invalid value?  Unfortunately, every time you try to do a select to see the value, you’ll get this same error.   So, we’ll have to run DBCC PAGE() in order to view the page data.  Start by turning on trace flag 3604 in order to print the results to the screen.

DBCC TRACEON (3604) 

Next, we’ll need to pass in both the file number and the page number to DBCC PAGE in order to see the contents.  This is contained within the original error message (screenshot below).  DBCC PAGE also takes a 3rd parameter for its print options, which is a number between 0 and 3.  Rather than describe them here, here is a link to the description.  In my scenario, I’ll be using print option 3, which is to show the page header plus the detailed per-row information.  I need this in order to be able to view the invalid data row.

DBCC PAGE ('DatabaseName', 1, 131693 , 3) 

image

The DBCC PAGE command will spit out the page information in text format.  I usually copy this out and paste it into Notepad++ (or whatever text editor you’re comfortable with) in order to make searching easier.  Here it is in the SSMS results pain:

Image(1)

So, now we’ve got a dump of the data, let’s look for the invalid row.  In notepad++, do a find on the column name that has the invalid data.  For example, in my case we’ll search on the CreateDate and look at the column values until we see something out of sorts:

image

Found it!  So, now I can scroll up to find the unique primary key value so we can update the column to  something not invalid:

image

Just to be sure, check to make sure that this record does indeed contain the invalid column:

image

Yup, sure does.  Now, as to what to the invalid date value for the column should be, who knows?  In my case, the dates of the other records around this invalid column were all only differing by milliseconds, and since we can assume that the CreateDate is just that, I doubt a difference of a few milliseconds is going to make a huge difference.  Make sure to check with whoever owns the data to make sure whatever value you need to replace is an acceptable value.

In my case, I just grabbed the next records’ CreateDate value and updated the invalid record to its value (the owners of the data had no clue what to set it to):

image

Okay, all fixed.  So, run DBCC CheckTable one more time to make sure there are no more errors:

image

Fixed.  Luckily, in my case it was just the one row.  Imagine having to do this to 1000 rows?  This is why you should be running your checks nightly if possible. 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: