Turn on CHECKSUM after an upgrade

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by David Baffaleuf » Turn on CHECKSUM after an upgrade

12 posts, Page 2 of 2

««1 2

Turn on CHECKSUM after an upgrade

Rate Topic

Display Mode

Topic Options

Author

Message

David BAFFALEUF

Posted Sunday, July 25, 2010 8:49 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 10:15 AM
Points: 61, Visits: 691

Hi dma33 & John

Linchi Shea has posted an analysis on the impact of page checksums and default trace with various IO workloads. You can review it here. I didn't want to go back there, as Linchi had already experimented and explained the most important facts. I just wanted to put the emphasis on the upgrade problem, and mention the readonly scenario.

John, if you choose to deactivate page checksums, you won't be warned when there are torn pages and bits trashed by anything lying under the file system. It is much safer to let the parameter to ON, it is not just another BP. Ask Paul Randal or any PFE or escalation engineer who has worked on a corruption case, and check the Data Corruption section on this forum, these 823, 824 and 825 errors are happening.

HTH

David B.

David B.

Post #958480

Vedran Kesegic

Posted Sunday, June 10, 2012 4:56 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 343, Visits: 1,076

To Steve's list I would add a step to rebuild all heaps and clustered indexe so ensure all pages have a checksum. Without that, checksum will be applied (and checked with DBCC CHECKDB) only to newly changed pages, not the existing ones:

The steps I follow when upgrading a DB from SQL 2000 or older:
1. Restore the DB to SQL 2005/2008.
2. Change compatibility level to 90 or 100.
3. Change page_verify to CHECKSUM
4. Rebuild all clustered indexes and heaps to ensure all pages have a checksum. For rebuilding a heap in sql 2008 there is "ALTER TABLE mytable REBUILD" command.
5. DBCC CHECKDB with data_purity (to ensure checksum is good to go)
6. DBCC UPDATEUSAGE (as a best practice to get the usage data up-to-date after the upgrade) -- sometimes needed to correct issues found by the data_purity check as well.

To rebuild a heap in SQL2005 you have to create clustered index and then drop it.

Best Regards,
Vedran

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1313625

« Prev Topic | Next Topic »

12 posts, Page 2 of 2

««1 2

Permissions