DBCC PAGE To The Rescue

Today, I was copying some databases from a SQL 2005 server to a shiny new SQL 2008 R2 server. And listening to Skrillex. As part of the operation, I was running DBCC CHECKDB on the databases after I restored each one. Now normally, I just use the NO_INFOMSGS, ALL_ERRORMSGS parameters, but since nobody was using these at the moment, I decided to add DATA_PURITY as well. To my utter astonishment (sarcasm), CHECKDB returned the following on one of them:

Msg 2570, Level 16, State 3, Line 1 

Page (1:2117757), slot 44 in object ID 78623323, index ID 1, partition ID 72057598521835520, alloc unit ID 72057598526029824 (type "In-row data"). Column "UPDATE_DATE" value is out of range for data type "datetime". Update column to a legal value.

Ouch. Note that if I hadn't been running DATA_PURITY checks as well, then I wouldn't have known about this until a user complained. To verify this, I ran the CHECKDB again without the DATA_PURITY option, and it came back with no errors. From what I understand, there is a bit more overhead when running DATA_PURITY checks, so if you can do this on a non-production environment, even better. For more info on the various DBCC options, check out Paul Randal's (blog | twitter) series on it: CHECKDB From Every Angle.

So I was ready to inform the application admin that there was a bad record in the database. But I can't just forward the error message to a non-DBA, who would be unfamiliar with the indicators above. I needed to be able to point her to the record in question.

Luckily, it was only one record, so I could look that up pretty easily, right?  Out of range is either below or above the standard datetime range for SQL, so I could just select MIN(CAST(UPDATE_DATE AS int)) from the table, or MAX, and see what each one returned, right? Wrong. Turns out SQL won't even try to convert it.  I also tried selecting the entire column, as INT to a temporary worktable to see if that would work. Nope. Lesson learned, there.

Then I remembered the handy DBCC PAGE command. All I needed was the page location, and the database name. and we should be able to locate the offending record. This worked even better than I imagined.

First, you have to turn on trace flag 3604 to get the output to come to the console instead of to the SQL error logs.

DBCC TRACEON(3604,1)
GO

Then I could run the DBCC PAGE command, using the database name, the page identifier shown in the beginning of the error message above, and the type of output I wanted to see.  There are four levels of output; 0,1,2, and 3, and I wanted the most detailed description of every row. So I chose option 3, and went for it.

DBCC PAGE('UserDatabase', 1, 2117757,3);
GO

This gave me an output that looked like this:

PAGE: (1:2117757)
*skip a whole bunch of stuff*
Slot 0 Column 6 Offset 0x4 Length 4 Length (physical) 4
ACCT_INT_CODE = 219                
Slot 0 Column 7 Offset 0x8 Length 4 Length (physical) 4
BAL_SK = 10011                      
Slot 0 Column 8 Offset 0xc Length 4 Length (physical) 4
CAL_SK = 104                         

Those are the individual row slots in the page, the column number and metadata.  Sweet. :-)  After a bit of searching for the column name in question, I came upon an entry that looked like this:

Slot 44 Column 4 Offset 0x28 Length 8 Length (physical) 8
UPDATE_DATE = INVALID COLUMN VALUE     

Perfect - now I could copy the rest of the information from that record into an email, and point the application admin to the exact record that needed fixed. If she can't fix it from the application side, I will attempt to update it from the database side. If that doesn't work for some reason, then we have the data, and can recreate the record, if needed.

Thanks,
-David.