November 30, 2006 at 11:55 am
Re: Maint plan not working, it sounds like a permission problem.
First set the Security, Authentication properties for Both Windows and SQL Server authentication.
Second, set Start up service account with "This account", use a domain account, a user who is an admin on the server. (Your network person can tell you if this person is an admin on the server).
The optimum is that this user also has an email account and Outlook is installed on the server. This makes sending SQL mail sooo much easier.
Three: Go into the properties of each job for the maintenance plan. This is found under Management/SQL Server Agent/Jobs. Look to see who is the owner. We solved this problem by creating a SQL server user that is an owner, DBO for all of our databases. (Create under Security/Logins. After selecting properties for this user, Under Database access tab, check them as db_owner). Then, we make them the owner of this job, rather than a trusted person as a domain user.
Others may have other suggestions, and this is mine.
November 30, 2006 at 1:51 pm
Thank you for your reply - a lot of info
i checked the jobs and they all have owner = system
should i use the dropdown to select a different user?
December 1, 2006 at 10:20 am
Are you doing any integrity checks in this maintenance plan? Maybe it's not your backup. Why would the job be attempting to run a "repair" command?
December 2, 2006 at 3:31 pm
Check your maintenance plan configuration... I think you have enable the option to repair the db incase of any errors...
When sql tries to repair the db as part of maintenace plan it will try to put the db in single user mode. I think There were other users in the db so that the maintenance failed with "Repair statement not processed. Database needs to be in single user mode." error.
Run dbcc checkdb manually against the db to check the corruption, if you don't have the previous checkdb out from the failed maintenance plan.
MohammedU
Microsoft SQL Server MVP
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply