Conistency Errors on SQL 7.0 DB

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 22, 2009 at 2:26 pm

  #931481

  The_SQL_DBA (1/22/2009)

  ---

  Paul, one question as a DBA what is the best way to avoid corruption in the first place..

  I'm not Paul, but...

  Ensure your IO subsystem is stable and performing properly. Corruption is, in the vast majority of cases, a hardware problem.

  Edit: I was thinking of another error. Nevermind...

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Paul Randal

  One Orange Chip

  Points: 29544

  More actions

  January 22, 2009 at 2:26 pm

  #931482

  Almost impossible to completely avoid corruption due to the complexity of the I/O subsystem. Best thing you can do is limit the chance for it happening and be able to recover as quickly as possible.

  So - make sure all drivers etc are up-to-date. Turn on page protection of some kind (torn-page or page checksums on 2005). Run regular DBCC CHECKDBs as that's the only thing that will check all pages in the DB. Have good backups that allow you to restore in the time you want. Implement an HA solution to allow failover when corruption occurs. Those are the basics and many are easier said than done.

  For more info, checkout the recording of the corruption talk I did at TechEd in Barcelona last November - see http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-80-minute-video-of-Corruption-Survival-Techniques-presentation.aspx

  Hope this helps.

  Paul Randal
  CEO, SQLskills.com: Check out SQLskills online training!
  Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
  SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
  Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

• The_SQL_DBA

  SSChampion

  Points: 10494

  More actions

  January 22, 2009 at 3:08 pm

  #931515

  Running CHECKDB is fine, to avoid performance issues I would be glad to do it on a test box, by grabbing a copy of the production backup. But even with that I run in issues like tempDb grows beyond 30Gb as the db I am running the CheckDB statement is 400Gb in size.

  Ideally I would like to know if there is a lighter version of CHECKDB that does not consume too much resources with CPU and also checks tempdb growth..if that were to happen the consistency checks could be run more often...it's just an open thought that I would like to put forth..

  Thanks again Paul, I will try to gain some knowledge from your excellent blogs..

  The_SQL_DBA
  MCTS

  "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

• Paul Randal

  One Orange Chip

  Points: 29544

  More actions

  January 22, 2009 at 3:13 pm

  #931521

  You're most welcome.

  Try the WITH PHYSICAL_ONLY option. You can also see how much tempdb space will be necessary using the WITH ESTIMATEONLY option.

  You might also find this useful - http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx

  Paul Randal
  CEO, SQLskills.com: Check out SQLskills online training!
  Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
  SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
  Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005