June 3, 2010 at 3:15 pm
I have a terabyte database ... weekly CHECKDBs are getting invasive and I am getting yelled at. *shrug*
Anyway, this db was migrated from 2000 to 2005 and then to 2008 ... stupid me, I only just NOW changed the Page Verify option from Torn-Page Detection to CHECKSUM. I want to use this to possibly scale back on my CHECKDBs. I do know that the system tables will not have checksums computed (unless I create a new db and migrate to it), and that this is NOT a replacement for CHECKDBs. I'm still in the deciding phase. However, what I practically need to know is this ... if i run a ALTER INDEX ... REBUILD on the clustered index, will that be enough to put a CHECKSUM on all the pages of my table? Or do I have to drop the clustered index and recreate it?
Thanks for your help!
😎
June 3, 2010 at 5:22 pm
Thanks ... but I think I have my answer ... BOL ... http://technet.microsoft.com/en-us/library/ms189858.aspx
ALTER INDEX ... REORGANIZE will only organize the leaf level pages of the clustered index which improves index scanning
ALTER INDEX ... REBUILD actually does drop the clustered index and create a new one. ERGO, every page in the table is touched and thusly gets a checksum applied.
😎
June 4, 2010 at 5:02 am
Page verification is not a replacement for checkDB, it's not going to safely allow you to reduce the frequency of CheckDB. Something still needs to read the pages and check the checksums.
The rebuild of all indexes (cluster and noncluster) will put checksums onto most pages. Some, like LOB pages may not be touched by rebuilds though.
What I would suggest regarding the CheckDB is a 2-part approach:
1) Take your backups with the CHECKSUM option. That means that the backup will read and check the page checksums. Not a replacement for CheckDB, but does do some of what checkDB does.
2) Restore a backup weekly onto a test/secondary machine and run checkDB against the restored copy. This both allows you to test your backups and to do integrity checks of the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2010 at 6:25 am
thanks. i know that checksums/backups with checksums are not replacement. however, ongoing weekly checkdbs are no longer feasible on my largest databases. i realize i have to come up a way to make them feasible, but my largest db is a TB and growing. a checkdb (even WITH PHYSICAL ONLY) takes over 9 hours, and the overhead generated by the checkdbs nearly makes my entire system unavailable. no solution is easy, and anything that makes the checkdb possible on a regular basis will cost money. so, utilizing the checksum will give me some peace of mind that the greatest source of my data corruption (i/o subsystem malfunctions) is being monitored.
thanks for your advice
😎
June 4, 2010 at 6:50 am
i will still absolutely be doing checkdbs, just not weekly
😎
June 4, 2010 at 7:58 am
Be careful. The minimum acceptable frequency of CheckDB should be influenced by the retention period of your backups. The idea is that checkDB should run often enough that, should it detect corruption, a restore from a clean backup is always an option.
What's your backup frequency, what's your backup retention?
Is doing a checkDB on a restored copy of the DB not an option? If not, why not?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2010 at 8:13 am
Deb Anderson (6/4/2010)
so, utilizing the checksum will give me some peace of mind that the greatest source of my data corruption (i/o subsystem malfunctions) is being monitored.
But it's not being monitored. Checksums alone don't detect corruption, it requires something to read the page and see that the page doesn't match the checksum for errors to appear. Corruption in an infrequently used table could hide for long periods of time before something reads the page and detects the bad checksum
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2010 at 9:36 am
Gail ... thanks again ...
I know that I need to run them weekly, and I am doing this currently. I don't feel like this is often enough especially since we are size constrained on my backup storage location, and my retention is not what it needs to be. But I'm doing good just to get them to let me do it this often. LITERALLY EVERYDAY, I worry about this very thing. I'm treading water, and I am trying to do everything I humanly can do to save us from ourselves. Enter checksum ...
Everything I've read says that when I do a backup with checksum, it will read each page. I do daily differential backups (which will touch all the latest work) and weekly full backups (which will touch all the data). Are you saying that this wouldn't be sufficent to detect that particular type of corruption? (which is the chief source of my corruption)
I'd love to have an environment where I can restore the dbs and and check them. I don't have that as an option at this time because we're constrained by our 1) data center power (its small, and we're literally on precipice of having too much equipment for the power running to it) and 2)convincing the people with the $$ that this is necessary. I am working now to resolve 2, my net admin is working on getting new servers with lower power requirements. So, I am VERY hopeful that this is all temporary. I do know that it would be a faulty assumption to rely on the checksum to make all my problems go away, I'm just hope for mitigation of the risk I currrently face.
Thanks again for your time
Deb
😎
June 4, 2010 at 9:50 am
Deb Anderson (6/4/2010)
Everything I've read says that when I do a backup with checksum, it will read each page. I do daily differential backups (which will touch all the latest work) and weekly full backups (which will touch all the data). Are you saying that this wouldn't be sufficent to detect that particular type of corruption? (which is the chief source of my corruption)
See the first post I made in this thread. It's OK, it's not a true replacement for checkDB, but it's better than nothing.
You didn't mention checksummed backups before, I wanted to be sure that you hadn't fallen for the myth that page checksums alone detect corruption when it occurs.
p.s. for restoring the backup and testing it, think that the powers that be would buy a 2 TB external drive? (or even buy it yourself and claim back). DBA at my last company did just that, it saved us a number of times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2010 at 10:21 am
Gail,
Sorry I wasn't more thorough in my question, I only initially wanted to know that my methodology for building the CHECKSUMS on the pages was valid. I'm used to using the DBCC DBREINDEX OR INDEXDEFRAG, so I was tentative about using the ALTER INDEX ... REBUILD since I didn't know what it was actually doing under the covers. Sadly, I do realize even adding checksums to the existing user tables, won't help me on the system tables - which I've also had corruption on in the past. *I love our SAN* I'm still essentially where I was even after applying a CHECKSUM, and I need to run that checkdb as often as I can.
I'm lone-wolf here and pretty much have been my entire 9 year career as a DBA, and I consider having another experienced DBA pick apart my thought processes as a luxury, so I am extremely grateful for the considerable time you put into responding to me on this thread. I have a MUUUCH clearer idea of my path as a result.
thanks once again, deb
ps ... seriously considering your idea of buying my own external hard drive 🙂
😎
June 4, 2010 at 10:39 am
GilaMonster (6/4/2010)
The rebuild of all indexes (cluster and noncluster) will put checksums onto most pages. Some, like LOB pages may not be touched by rebuilds though.
If the IO subsystem has caused corruption in the past, why has it not been investigated and fixed?
p.s. I'm not a DBA. 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply