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 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 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 look on data purity things after DB upgrade from prior versions of SQL Server 2005.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)