maintenance planner

[masds]

In maintenance plan wizard, if you choose check database integrity and repair minor errors, the wizard will create a job for it, the command will be :

dbcc checkdb(<db>, REPAIR_FAST) WITH NO_INFOMSGS

Run this command need to be in single user mode. So this job will always fail.

It seems incredible that the maintenence plan wizard can generate a job like this, I think I must be wrong somewhere, can somebody point me to the right direction?

 

[cyno]

I think this does not needs to run under single user mode. Anyways take a look at the owner of the job. And try to add SA as job owner.

Cyno

 

[mrdenny]

It only needs to be run in single user mode if there is a problem. Run dbcc checkdb ({database}) and see what the problem is.

Your nightly plan should only check the database. You should manually repair the database so that you can control to what level the repairs are done.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(My very old site)

 

[masds]

Thanks for the help!

And try to add SA as job owner

This is quite confusing to me, the user who runs the SQL Agent service is not the owner of the job, the owner of the job can be 'sa', if the job involves accessing local machine or network resource, what kind of permission it get?

It only needs to be run in single user mode if there is a problem.

Following command is inside the job:

dbcc checkdb(<db>, REPAIR_FAST) WITH NO_INFOMSGS

it always need single user mode, I ask the question in this thread is to know if it is a maintenance planner's bug.

Your nightly plan should only check the database. You should manually repair the database so that you can control to what level the repairs are done.

What the output of the nightly job? it just populate a table and I can look that in the morning and decide how to fi the problematic databases?

 

[Catadmin]

If a job's owner is SA, it pretty much has Sysadmin access to run itself when it is scheduled. This way, if the creator of the job doesn't have the proper permissions, the job can still run.

A note, though. Usually only a SysAdmin can change the job owner to SA. I've tried it as a non-SysAdmin user with other Fixed Server Role permissions and I was unable to get to that box.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!