Data Purity issue after upgrading SQL 2000 to later versions

Rohit Garg, 2013-08-20

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/)

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads