February 21, 2013 at 12:06 pm
I have a table where I am unable to run a simple select statement from. I ran DBCC Checktable on it and got the following back:
DBCC results for 'Employees'.
Msg 2570, Level 16, State 3, Line 1
Page (13:41227), slot 3 in object ID 634485339, index ID 1, partition ID 72057594311475200, alloc unit ID 72057595234484224 (type "In-row data"). Column "Employee_Number" value is out of range for data type "decimal". Update column to a legal value.
There are 7175282 rows in 250903 pages for object "Employees".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'Employees' (object ID 634485339).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This was originally a sql server 2000 table - but has since been migrated to 2008. Obviously I need to find that value and fix it - but what is the best way to do that? Any suggestions or links to similar topics with this problem?
February 21, 2013 at 12:39 pm
srobinson 596 (2/21/2013)
I have a table where I am unable to run a simple select statement from. I ran DBCC Checktable on it and got the following back:DBCC results for 'Employees'.
Msg 2570, Level 16, State 3, Line 1
Page (13:41227), slot 3 in object ID 634485339, index ID 1, partition ID 72057594311475200, alloc unit ID 72057595234484224 (type "In-row data"). Column "Employee_Number" value is out of range for data type "decimal". Update column to a legal value.
There are 7175282 rows in 250903 pages for object "Employees".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'Employees' (object ID 634485339).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This was originally a sql server 2000 table - but has since been migrated to 2008. Obviously I need to find that value and fix it - but what is the best way to do that? Any suggestions or links to similar topics with this problem?
Run this
DBCC TRACEON (3604)
GO
--dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
DBCC PAGE (DbName, 13, 41227, 3);
find a record that is in slot 3 and look for 'INVALID COLUMN VALUE' then update the value to correct one. Run again CHECKDB
February 21, 2013 at 2:00 pm
Take a look at this article. http://www.sqlservercentral.com/articles/65804/, the section on data purity errors.
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
February 21, 2013 at 2:25 pm
Thank you so much sir - fixed the problem!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply