• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

check database how often

how often should the check database command be run, as a good standard?

0
25112
Asked:
25112
  • 6
  • 3
  • 2
  • +2
7 Solutions
 
25112Author Commented:
there are 2 thoughts about in my organization...

some are saying.. database can be corrupted anytime somehow.. so it is best to run dbcc checkdb every night..

others are saying sql server is robust.. and does not fail easily (data corruption).. so check db dbcc should be run only as needed..

which approach is correct in which circumstance?what is your experience and the standards in this.
0
 
mensoidCommented:
In my organization we rely more on the backups and running process to recover/detct issues, as such the checkDB command is only executed with the maintenance plan which is setup for a weekly full backup and nightly incremental backups. Detection of a significant corruption, for us, occurs instantly via application failure, minor ones are corrected by weekly db restorations (depending on DB size). I would suggest you ask these questions:
1) How often, if ever has the command found/correcetd issues?
2) Does the process interfere with our production environment?

if you answer often to question 1, I think you have to look at underlying hardware issues, without a corruption of the used RAID drives, I can't say we've ever (last 10 years) had a corruption that could be fixed by CheckDb. The times we have had issues, mutliple HD failures of a RAID, or tramatic machine shutdown while the DB was in an intensive high risk operation, we've had to rely on the backups to correct the issue.
2) If the answer here is no, then I guess I'd say what do you have to lose, run it nightly, it it find something and fixes it great, but otherwise it won't impact your operations. If the answer is yes, then I'd probably only run it on server startups, and maybe weekly just before a full DB backup to make sure your backing up the best data.
0
 
sachitjainCommented:
It actually depends on size, frequency of usage and criticality of your dbs. If usage and criticality are high then you need to run  check dbcc daily. If not and volume of data is also low or medium, you could be fine with running it once weekly.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Ramesh Babu VavillaCommented:
least DBCC CHECKDB  command should be executed once in  a month
0
 
25112Author Commented:
>> you have to look at underlying hardware issues, without a corruption of the used RAID drives
Are you suggesting improper Hard drive setup can corrupt databases? Were you able to prove that it was the HDD (other than the outright crash)?

>>we've had to rely on the backups to correct the issue.
It is alarming that CHECKDB can't fix data, but just get rid of bad data?

>>run it nightly, it it find something and fixes it great,

hmm.. yes, checkdb is intensive on some databases and no others.
0
 
25112Author Commented:
. just running "DBCC CHECKDB (dbname)" is no good, right?
0
 
mensoidCommented:
>>Are you suggesting improper Hard drive setup can corrupt databases? Were you able to prove that it was the HDD (other than the outright crash)?

     We've seen issues on RAID configured servers where the RAID controller card was beginning to fail, this resulted file/data corruptions without any warning on a system that continued to function. the most notable hidden danger we've recently had is the battery backups on the RAID controllers, the symptom is that the machinewill work fine, pass all tests, but if a sudden power failure occurs we'd end up with varying levels of corrupt data. We've also seen issues with software driven RAID systems (as opposed to a harware RAID controller) and mix-matched drives (size/speed) with high data throughput.
-----------------
>>It is alarming that CHECKDB can't fix data, but just get rid of bad data?

    Check DB rebuilds indexes which can result in abnormally long query run times, query failures, and in rare cases incorrect results (depending on DBS). I think to be honest it depends on what your DB is really doing. If your often adding/droppping indexes or creating them on the fly you may have a higher need for checkDB, in our environment we're adding approx 200MB of data a day, and removing approx the same with a roughly static size of approx 190GB data file. The indexes are set in place and not changed in thier definition, and as I said we still run the checkDB weekly with the full backups. As such if the indexes are corrupt checkDb wil fix them, if the data is corrupt your out of luck.

Review this article I think it says it very well, both the risks, and the potential values
http://www.sql-server-pro.com/dbcc-checkdb.html

>>hmm.. yes, checkdb is intensive on some databases and no others.
 the  WITH PHYSICAL_ONLY option may allow it ro run a bit quicker, but again risk vs reward, and always make sure your backups are good and frequent.

>>just running "DBCC CHECKDB (dbname)" is no good, right?
given that the dbname has to be in single quotes ie:
DBCC CheckDB ('MyDb')
That's fine, it may be  abit verbose in the messgaes that are returned, but otherwise it's fine, it will report any issues, but not attempt any repairs.



0
 
Scott PletcherSenior DBACommented:
First, make sure you have CHECKSUM set in the SQL options for the database so that SQL itself can best detect corrupted pages.

IF you alert yourself as soon as SQL detects a problem, and your RAID is generally very reliable, you can get away with infrequent DBCC runs, say every month or two.

I check master every day, because it is small and so critical.

Msdb seems to corrupt more than any other db (in my experience) so I like to check it daily too unless it is very large (almost always caused by lots of packages being stored in it).

Similarly, for small user dbs, I check them every day or at least once a week, depending on how critical they are.

For large user dbs, you by necessity check them less often, since it takes so long to do the check and the checks cause intent-shared locks.

Btw, the output is vastly easier to read if you specify the WITH NO_INFOMSGS option:

DBCC CHECKDB( ... ) WITH NO_INFOMSGS
0
 
25112Author Commented:

>Check DB rebuilds indexes which can result in abnormally long query run times
are you referring to DBCC CHECKDB  ('dbname',REPAIR_REBUILD)?

and this will affect only non clustered index, right?
0
 
25112Author Commented:

>>make sure you have CHECKSUM set in the SQL options

is this part of the DBCC or seperate?
0
 
mensoidCommented:
>>and this will affect only non clustered index, right?
  Let me clarify, I just realized that could of been mis-read. A corrupt index can result in abnormally long query times, not the running of CheckDb. Secondarily, running repair_Rebuild will only likely repair a non-clustered index correct. A corrupt clustered index is effectivly corrupt data, and non-"repairable" expect by restoring the DB. as ScottPletcher: posted, the Checksum flag will allow MSSQL to dectec the issue, but still not correct it.
0
 
Scott PletcherSenior DBACommented:
>>>> >>make sure you have CHECKSUM set in the SQL options
is this part of the DBCC or seperate? <<<<

Sorry, that's separate.

Run this query for your "databasename":

SELECT page_verify_option_desc
FROM master.sys.databases
WHERE name = N'databasename'

If that does NOT return "CHECKSUM", set it:

ALTER DATABASE databasename SET PAGE_VERIFY CHECKSUM

Then (eventually) SQL will better know if / when an error occurs and put a message in the log for you to catch it as early as possible.  [SQL won't actually switch each db page to CHECKSUM until that specific page gets rewritten.  So it may take a while to switch every page over to using CHECKSUM.]

But, as mensoid stated, that by itself will not fix the error, it just gives you more accurate and faster notification of the error.
0
 
25112Author Commented:
thanks a lot
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now