Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Turn on CHECKSUM after an upgrade Expand / Collapse
Posted Sunday, July 25, 2010 8:49 AM

Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 29, 2016 9:37 AM
Points: 61, Visits: 712
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.


David B.

David B.
Post #958480
Posted Sunday, June 10, 2012 4:56 PM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 14, 2016 4:53 AM
Points: 370, Visits: 1,252
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,

Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers - Transaction log myths
Post #1313625
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse