I would personally never use a maint plan - always preferring to write the jobs in clear tsql - beware that sysmaint.exe works as an external process to sql server. Anyway in the maint plan setup there is an option to fix errors on checkdb - don't choose this as this dbcc command rightly needs exclusive single user mode to run - if you checked this box clear it and your maint plan should run.
Generally you need dbo rights to work on integrity / optimisation jobs ( and the agent service needs rights too ) Servers normally have local admin rights ( or close to ) for the server service account and agent service - this allows jobs to run and dbcc commands to run without error. Although it's not ideal to have your server as the local admin of its box you'll find most jobs/activities work much better!!
Did anyone find a solution. I am getting this error, using log shipping, but in the log shipping maintenance job, the backups were successful. The error is coming when the standby server tries to restore the transaction logs. I have no idea what this error means
I've this error on one of my full db backup job. Indeed the error msg didn't help much but I found it in ntevent viewer that my harddisk is full. So just wonder did you guys check the NTEvent log
I was having the same problem when I found this article:http://support.microsoft.com/?kbid=303229
I am a backup-ignorant but it seems that when the recovery model is set to "simple", then it is not possible to backup the log db (probably because it would be useless). This is why the related scheduled job was failing while the same job for the db (and not the log) was working fine.
I think that the Enterprise Manager should check the recovery model used for the specific database and prevent users to try to backup the log while it is not possible.
Or, perhaps, I should read some books before playing with backups...
if you contiue to get this error. you should check your DB recovery model. if it is set to simple, then you will get following error:
Executed as user: <user>. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Set your DB recover model to FULL.
I hope this helps in additon all other comments posted.
Folks, I really want to thank you all for the tips. They really solved a big boring problem. I hope be usefull sometime.