Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Turn on CHECKSUM after an upgrade


Turn on CHECKSUM after an upgrade

Author
Message
David BAFFALEUF
David BAFFALEUF
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 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.

HTH

David B.

David B.
Vedran Kesegic
Vedran Kesegic
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1255
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

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search