asked
Consistency error after upgrade from SQL 2000 to 2008 R2
Yesterday I have migrated one of our SQL 2000 databases to 2008 R2 and I modified the compatibility level, run update statistics, and run DBCC and it comes clean. However after I set up [Ola's script][1] for backup, index maintenance and integrity check, the job that that checks integrity fails with the following error: DateTime: 2010-08-19 09:52:10 Server: MyServer Version: 10.50.1600.1 Edition: Enterprise Edition (64-bit) Procedure: [master].[dbo].[DatabaseIntegrityCheck] Parameters: @Databases = 'USER_DATABASES', @PhysicalOnly = 'N', @NoIndex = 'N', @ExtendedLogicalChecks = 'N', @Execute = 'Y' DateTime: 2010-08-19 09:52:10 Database: [DB1] Status: ONLINE Mirroring role: None Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL DateTime: 2010-08-19 09:52:10 Command: DBCC CHECKDB ([DB1]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 2 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 4 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 7 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 8 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 10 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 11 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 16 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 17 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 18 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 20 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 22 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 24 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 25 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 27 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 28 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 29 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 32 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 33 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 34 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 35 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 36 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. Msg 2570, Level 16, State 3, Server MyServer, Line 1 Page (1:374), slot 37 in object ID 642101328, index ID 1, partition ID 323555729342464, alloc unit ID 42080752631808 (type "In-row data"). Column "PAY_PRIORITY" value is out of range for data type "numeric". Update column to a legal value. CHECKDB found 0 allocation errors and 22 consistency errors in table 'APP_SUBSYSTEM' (object ID 642101328). CHECKDB found 0 allocation errors and 22 consistency errors in database 'DB1'. Outcome: Failed Duration: 00:01:40 DateTime: 2010-08-19 09:53:50 DateTime: 2010-08-19 09:53:50 I again manually runs dbcc checkdb and found 0 errors `CHECKDB found 0 allocation errors and 0 consistency errors in database 'DB1'.` I will email Ola but in the mean time please help me make sense of the descrepancy. BTW I have found a KB article from Microsoft regarding this issue which basically says this error is only fixed manually: [Troubleshooting DBCC error 2570 in SQL Server 2005][2]. [1]:
http://ola.hallengren.com/ [2]:
http://support.microsoft.com/kb/923247
You will have to fix this manually. Paul Randall has an excellent article [Pauls DBCC][1] **Column value corruption** Here's an example of this on SQL Server 2005: Msg 2570, Level 16, State 3, Line 1 Page (1:152), slot 0 in object ID 2073058421, index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type "In-row data"). Column "c1" value is out of range for data type "datetime". Update column to a legal value. This is where a column has a stored value that is outside the valid range for the column type. There are a couple of repairs we could do for this: 1. delete the entire record 2. insert a dummy value (1) isn't very palatable because then data is lost and it’s not a structural problem in the database so doesn't have to be repaired. (2) is dangerous - what value should be chosen as the dummy value? Any value put in may adversely affect business logic, or fire a trigger, or have some unwelcome meaning in the context of the table - even a NULL. Given these problems, I chose to allow people to fix the corrupt values themselves. [1]:
http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Can-CHECKDB-repair-everything.aspx
I just run `DBCC CHECKDB` and the result comes clean, but I haven't altered the original Ola script and it catches the errors. Here is what microsoft says: "For databases created in SQL Server 2005, these checks are enabled by default and cannot be disabled, so the use of the DATA_PURITY option when executing a DBCC CHECKDB or DBCC CHECKTABLE command is irrelevant." I am assuming this also applies to my case since I am on 2008R2 even though the database was originally created in SQL 2000. and I expected `DBCC CHECKDB` would catch the problem.
