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.
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)
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:
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:
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:
Just to be sure, check to make sure that this record does indeed contain the invalid column:
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):
Okay, all fixed. So, run DBCC CheckTable one more time to make sure there are no more errors:
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.