- Error number
- 2570
- Severity
- 16 (user-correctable error)
- State
- 2 or 3 in Microsoft's documented DBCC output (2 for an nvarchar column, 3 for real, decimal and datetime)
- Product
- Microsoft SQL Server Database Engine
- Versions
- All supported SQL Server versions (2016 to 2025)
- Category
- Corruption & Consistency
- Last reviewed
- 24 October 2026
On this page
What it means
Error 2570 is reported by DBCC CHECKDB or DBCC CHECKTABLE when a stored column value is impossible for its data type (for example a datetime outside its range or a float that is not a number); DBCC cannot repair it, so you correct the row yourself.
Besides checking pages and structures, DBCC runs "data purity" checks: it decodes column values and confirms each one is legal for its data type. Microsoft gives examples: a Unicode value must have an even byte length, a datetime must lie between 1 January 1753 and 31 December 9999, and real or float must not hold special values such as NaN or infinity. Types whose storage cannot hold an illegal value (such as tinyint) are not checked. One 2570 is printed for every bad value.
Read the message from left to right: the page and slot locate the row, the object, index and partition IDs name the table, and the end names the column and the data type it violates. Unlike most consistency errors, 2570 is about the value, not the page structure, and DBCC repair options cannot fix it because DBCC cannot know what the value should be. The table usually stays readable, but queries on the column can return wrong results, fail, or trip other errors (Microsoft mentions access violations and error 9100 when statistics are built).
Common causes
- An application or driver sent an invalid value through a remote procedure call (RPC) that was stored without validation, which Microsoft names as the main source.
- A database upgraded from a much older version that already held invalid values; data purity checks only become automatic after DBCC CHECKDB WITH DATA_PURITY has run cleanly once on such a database.
- Physical damage on the storage that changed the bytes of a value on an otherwise valid page.
- Bulk loads or third-party tools that wrote native-format data without the engine validating the values.
Diagnose it
Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.
Run the data purity check on one table
Read-only without a repair option. Faster than a full CHECKDB when you already know the table from the message.
DBCC CHECKTABLE (N'dbo.Orders') WITH DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS;No output means every value is legal. Otherwise one 2570 per bad value, each with the page, slot and column.
Check the whole database, including value checks
Heavy on I/O and CPU: run it off-peak or on a restored copy. Do not use PHYSICAL_ONLY here, because it skips the data purity checks.
DBCC CHECKDB (N'AppDb') WITH DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS;The list of tables and columns with invalid values, plus any structural errors. Structural errors (other numbers than 2570) need the restore-first approach for corruption; 2570 alone needs data correction.
Which table and index do the IDs in the message name?
SELECT OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
OBJECT_NAME(p.object_id) AS table_name,
i.name AS index_name, au.type_desc, p.partition_number
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON au.container_id = p.partition_id
JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE au.allocation_unit_id = 72057594049331200; -- alloc unit ID from the messageThe table, index (index ID 0 is a heap, 1 the clustered index) and partition that hold the bad value.
Find the rows with the invalid value (by data type)
Microsoft's queries, adjusted to a table key. Use the one for the data type named in the message.
-- nvarchar / nchar: a Unicode value must have an even byte length
SELECT OrderId, DATALENGTH(CustomerName) AS bytes
FROM dbo.Orders
WHERE DATALENGTH(CustomerName) % 2 <> 0;
-- float: values outside the legal ranges (NaN and infinity end up here)
SELECT OrderId, Amount
FROM dbo.Orders
WHERE Amount <> 0.0
AND (Amount < 2.23E-308 OR Amount > 1.79E+308)
AND (Amount < -1.79E+308 OR Amount > -2.23E-308);
-- datetime: outside 1753-01-01 to 9999-12-31
SELECT OrderId
FROM dbo.Orders
WHERE OrderDate < '17530101' OR OrderDate > '99991231 23:59:59.997';The keys of the rows to correct. If these queries return nothing although DBCC reports 2570, locate the row from the page and slot on a restored copy, as Microsoft describes.
How to fix it
Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.
Decide the correct value for each row
Read-onlyDBCC repair cannot fix 2570, and no repair option (including repair with data loss) helps. Microsoft lists the choices: the real value if you know it (from the source system or an older backup restored side by side), an agreed default, NULL, the minimum or maximum of the type, or deleting the row if it is useless without the value. Decide this with the owner of the data, row by row.
Correct the rows by their key
Changes dataUpdate only the rows found by the diagnostics, inside a transaction, then run DBCC CHECKTABLE again until it prints nothing.
Caution: Take a backup first and change only the rows you identified, by primary key. Check the row count before you commit.
BEGIN TRANSACTION;
UPDATE dbo.Orders
SET OrderDate = NULL -- or the value agreed with the data owner
WHERE OrderId = 1042; -- the key found by the diagnostics
-- expect (1 row affected), then:
COMMIT TRANSACTION;
DBCC CHECKTABLE (N'dbo.Orders') WITH DATA_PURITY, NO_INFOMSGS;Fix the source of the invalid values
Client or network sideFind the application, driver, import job or tool that writes the column and make it send valid typed values (for example map NaN and infinity to NULL before sending floats). Otherwise new 2570 errors appear after the next check.
If structural errors appear too, treat it as corruption
Changes database stateWhen CHECKDB also reports page or allocation errors, or 823 and 824 errors appear in the log, the value is a symptom of damaged storage. Fix the storage path and restore from a clean backup, as for error 824.
Caution: A restore replaces the database or pages. Back up the tail of the log first so no committed work is lost, and rehearse on a copy.
Examples
DBCC CHECKTABLE finds a datetime value out of range
Shown as documented by Microsoft (sample DBCC output in the MSSQLSERVER_2570 reference, with table and column names adapted); not reproduced here, because SQL Server rejects out-of-range values on every documented way of writing them. The IDs are illustrative.
DBCC CHECKTABLE (N'dbo.Orders') WITH DATA_PURITY, ALL_ERRORMSGS;Msg 2570, Level 16, State 3, Line 1 Page (1:157), slot 0 in object ID 581577110, index ID 0, partition ID 72057594043039744, alloc unit ID 72057594049331200 (type "In-row data"). Column "OrderDate" value is out of range for data type "datetime". Update column to a legal value. There are 3 rows in 1 pages for object "Orders". CHECKDB found 0 allocation errors and 1 consistency errors in table 'Orders' (object ID 581577110).
How to prevent it
Validate values in the application and use typed parameters, so NaN, infinity and impossible dates never reach the database. For databases that came from old versions, run DBCC CHECKDB WITH DATA_PURITY once; after a clean run, the checks are part of every normal CHECKDB.
Run full DBCC CHECKDB regularly (not only PHYSICAL_ONLY, which skips value checks), either on the server off-peak or on restored copies, so invalid values are found before they produce wrong results.
Common questions
Can REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS fix error 2570?
No. Microsoft states that data purity errors cannot be repaired by any DBCC repair option, because DBCC cannot know the right value. You update or remove the affected rows yourself.
Is 2570 a sign of disk corruption?
Not usually. Most cases come from invalid values sent by an application or carried over from old versions. If CHECKDB also reports structural errors, or the log has 823 and 824 errors, investigate the storage as well.
Do I need WITH DATA_PURITY every time?
No. Value checks are on by default; the option is only needed once for databases upgraded from older versions. PHYSICAL_ONLY skips them, so a regular full CHECKDB is still needed.
Learn more on CodeWithSQL
Official documentation
Last technically reviewed 24 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.