November 30, 2006 at 10:39 am
Hello
I'm very new to SQL (we use 2000)
I set up a maint plan which had been working great until last week when i had to reboot our servers for MS updates.
Then I noticed that the backups were failing. I looked in the error logs and saw
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'APPSERV' as 'system' (non-trusted)
and....
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
I found that unusual since my sql agent login is a windows login which was created for sql. So, i don't know why 'system' is showing up.
I think a restart of my sqlagent service fixed the situation but I'm wondering why this happened?
One more thing, can someone tell me what sp_msdbuseraccess does?
Thanks all
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply