How frequent db integrity checks should be

[divinyl]

Hi all

Due to a bug in SQL 2000 whereby db integrity checks fail if selecting the "Attempt to repair minor problems" - see KB

http://support.microsoft.com/?id=290622

- it looks like i'm going to have to schedule downtime with the users so that a script can be run that kills all connections to the databases and then runs the integrity checks. The users work 24x7 so I plan to do this only once a month. Is this really frequent enough though??

Thanks

Div

 

[mrdenny]

That's not actaully a bug. It's designed to work that way.

It's recommend to run the maint plans with out the attempt to replain minor problems box checked. Then read the reports daily. If a problem is reported in the log you can then schedule downtime to fix the problem manually.

For a database that needs to be up 24x7 I would say that monthly isn't enough. If it was one of my systems I'd check it weekly.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[divinyl]

Microsoft call it a bug though. Follow the link i sent you -it says BUG. You replied to another of my posts saying that you recommend i do leave the box checked (so allow SQL to fix the minor problems). Do you advise against this now?

As i am new DBA, i never really looked into the type of problems picked up by the integrity checks, nor how many - but couldn;t disabling this option and then trawling through the logs potentially be a huge maintenance task? I suppose I can try it out for a while...

 

[mrdenny]

That wasy probably a typo on my part. Sorry, I don't remember the specific thread. I reply to a lot of threads and they all start to run together after a while.

Let's put it this way. On the servers that I have that are setup for Maint Plans, the box is unchecked.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[divinyl]

So.... is it not a big task to trawl through the logs every week after these maint plans have run and fix all the reported problems? And are these normally straight forward problems that can be easily fixed, or do they sometimes require research? I don't really know the types of issues that would potentially occur, nor the frequency..

I'm just thinking that across 50 servers, having to check the logs every week and fix problems may be a bigger headache then scheduling say fortnightly downtime whereby these checks can run in single user mode without failure.

What are your thoughts?

Thanks

 

[mrdenny]

All you need to do is scan the logs for an error, then run DBCC CHECKDB against the database in question.

This will tell you what the recovery level needs to be to fix the problem. Then you can take the database into single user mode and fix the problem.

Typically there shouldn't be any errors that need to be fixed. If you have good solid servers and storage then errors will be few and far between.

There isn't any point in kicking out the users if you don't have to.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[divinyl]

Thanks Denny - this is good advice. Will do it this way.

Cheers

Div

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com