page verifacation and DBCC CHECKDB

From what I have read, it is a recommended best practice to run DBCC CHECKDB over all databases, and to set page verifacation to CHECKSUM for all databases.

My questions are;

1-Are both these procedures used to identify corruption?
2-If so are they looking for the same type of corruption, or different?
3-Are they duplicating the same task?
4-What kind of issues lead to data corruption - and is it easy to recover from?

(not a DBA!)
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
>>1-Are both these procedures used to identify corruption?

Yes - among other things - and how they are used are different.

Setting PAGE_VERIFY option to CHECKSUM computes a checksum every time a page is written to disk and stores it with the page.  When the page is read later, the checksum is recomputed and compared to what's stored on disk.  If the values are not the same you have a possible corruption.

So using PAGE_VERIFY = CHECKSUM is an on the fly kind of thing that happens real time as pages are read and written.

Links to Docs: https://msdn.microsoft.com/en-us/library/bb402873.aspx

DBCC CHECKDB is a procedure you run periodically that does a whole bunch of other things to to ensure the internal integrity of your database.  I won't go into the details of it but refer you to Books Online: https://msdn.microsoft.com/en-us/library/ms176064.aspx

>>2-If so are they looking for the same type of corruption, or different?

Well as mentioned above they are doing part of the same things and CHECKDB does extra as well.

>>3-Are they duplicating the same task?

I would say no.   PAGE_VERIFY is real time, page by page as needed type of verification.

CHECKDB is more of a whole of database kind of thing.

>>4-What kind of issues lead to data corruption - and is it easy to recover from?

How long is a piece of string?  Who knows what could cause corruption.  Bad spot on the disk?  Power surge causes a malfunction in the nano second that a page is being written to disk?  Sun spots ?  Someone gets too close to your disk with a really strong magnet ?  Generic Hardware failure?  The list is endless.

How easy is it to recover from?  How good are your backups ?

If you discover a bad page there are ways to restore just that particular bad page (if it's archival data) or you may need to restore the whole database (let's say the bad page contains cash on hand balance in a heavily updated accounting application - this value could change frequently, so restoring to a value an hour ago isn't necessarily going to give you the most accurate results).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.