Cannot run integrity check if Attempt to repair is checked?

[cooperchien]

I am trying to run database integrity check in database maintenance plan. I found that if checked the check box of
Attempt to repair any minor problems, the integrity job will fail. If I dont check the box, the job will succeed.
My other jobs such as backup and database optimization are working perfectly.
Can anyone tell me why?

 

[mrdenny]

The job is failing because there is something that it is trying to use and there is one or more user currently using the database.

Run DBCC CHECKDB in Query Analyzer. It will tell you how to repair the issue.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com

 

[cooperchien]

My SQL server is running 24/7. Therefore, does that mean I cannot repair any minor problem during the integrity check?

Thank you for your help.

 

[Catadmin]

Pretty much. If someone is connected to the database, regardless of whether or not they're actually using the connection, you can't repair data or you might corrupt something that they are updating/deleting/inserting.

Your only option is to create a "maintenance window" and notify your users that the db will be unavailible on a nightly/weekly basis at X time for X amount of hours for preventive maintenance work.

Or you could just kick everyone off with KILL <spid> and set the DB to Single User, but that could cause problems too.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

Pretty much. Have the job send you the log every time that it runs without the auto fix option. Review the log every time that it comes in. If it says that there is a problem schedule an emergency window to correct the issue.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com

 

[mutley1]

I'm guessing your maintenance plan is doing all databases?

If so, you're going to have a prob with the master....check the log and i think everything else would have run thru.....but check out this link.

http://support.microsoft.com/kb/290622

HTH,

M.