Data Purity Issue After Upgrading SQL 2000 to Later Versions

Introduction

In SQL Server versions prior to 2005, it was possible to have invalid data values into a database. That can lead to incorrect result or issue at the time of query execution.

SQL Server 2005 brings new option to the DBCC CHECKDB and DBCC CHECKTABLE commands to solve this issue. "DATA_PURITY”, The new option to check for issues where column values are not valid or out-of-range. In SQL Server 2005, data purity check is enabled by default & performed each time DBCC CHECKDB runs on database. But this is not the same for upgraded databases.

For upgraded databases, we need to run below DBCC command on database & In case DBCC CHECKDB with DATA_PURITY got completed successfully, a bit dbi_dbccFlags will be flipped from 0 to 2. Now from future onwards, data purity is enabled by default for upgraded databases as well & it will be performed each time DBCC CHECKDB runs.

DBCC CHECKDB with DATA_PURITY

How to check Data purity for your SQL instance? 

DBCC TRACEON (3604)
GO
CREATE TABLE #DBCC (
PARENTOBJECT VARCHAR(255),
[OBJECT] VARCHAR(255),
FIELD VARCHAR(255),
[VALUE] VARCHAR(255)
)
CREATE TABLE #DBCC2 (
DATABASENAME VARCHAR(255),
PARENTOBJECT VARCHAR(255),
[OBJECT] VARCHAR(255),
FIELD VARCHAR(255),
[VALUE] VARCHAR(255)
)
EXEC MASTER.DBO.SP_MSFOREACHDB
‘USE ? INSERT INTO #DBCC EXECUTE ("DBCC DBINFO WITH TABLERESULTS");
INSERT INTO #DBCC2 SELECT "?", * FROM #DBCC;
DELETE FROM #DBCC’
SELECT DATABASENAME,
(SELECT VALUE FROM #DBCC2 WHERE DATABASENAME = A.DATABASENAME AND _
	FIELD = ‘DBI_DBCCLASTKNOWNGOOD’) LASTGOODCHECKDBDATE,
(SELECT VALUE FROM #DBCC2 WHERE DATABASENAME = A.DATABASENAME AND _
	FIELD = ‘DBI_CREATEVERSION’) DBCREATEVERSION,
(CASE VALUE
WHEN 0 THEN ‘DISABLED’
WHEN 2 THEN ‘ENABLED’
END) DATAPURITY
FROM #DBCC2 A
WHERE FIELD = ‘DBI_DBCCFLAGS’ AND
VALUE = 2 AND
DATABASENAME NOT IN (‘MASTER’,'MODEL’)
DROP TABLE #DBCC
DROP TABLE #DBCC2
GO

Sample Result

Key Points

1. In SQL 2005 & above, data purity is enabled by default that means dbi_dbccFlags flag value will be 2 only.
2. For Master & Model dbi_dbccFlags flag value will be 0 only
3. For DB upgraded from SQL 2000 or having dbi_createVersion value lower than 611 that means dbi_dbccFlags value will be 0 till the first time DBCC CHECKDB with data purity completed successfully

4. If dbi_dbccFlags flag value is 2, that means data purity is enabled by default

Possible Error Message 2570

Refer: http://support.microsoft.com/kb/923247

DBCC results for "Object1".
Msg 2570, Level 16, State 2, Line 1

It’s always recommended to have a look at data purity things after DB upgrade from prior versions of SQL Server 2005.