While looking for the solution we saw that the maintanance plan as it is was running on databases that didn't exist anymore. We did not see the plan under the normal maintanance plan. When you start SQL enterprise manager (2000) expand management / SQL Server Agent / Jobs. Here are the faulting jobs. If they run on databases that do not exist simply remove the job.
If the failure reason is: SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' of component 'Agent XPs' because this component is turned off see EV100233
(Warning Event ID 208).
This has happened when trying to get the job to produce a Transaction Log for the “master” db. Transaction log backups of master database are not allowed. See ME303229
for more information.
We ran into this error on a 3-node cluster. When using a local account, it would work fine until it failed over to another node. Trying to use a domain account did not work. It turns out that the domain account needed to be added to the Log on Locally security policy on all three nodes.
Make sure that the job is run under the same account that the SQL Server Agent runs under. This has fixed all of our problems regarding this error.
As per ME308988
: "Names that include embedded spaces are not submitted correctly through the stored procedure that is submitted to the SQL server. To work around this problem, avoid using spaces in the file name that is required for the Direct Mailer. Note that if you surround the file name with brackets, the space in the file name is handled correctly".
There is a Q article (ME241643
) that points towards this error but specifies that this problem is NT 4.0 specific ( one of the solutions is to upgrade to w2k server). However this problems can also occur on Windows 2000 servers.
A suggested approach is to select the job history, and choose to the the step details, , , ( it is a check box in the upper right corner)..you should then see the step detail ( and maybe a better error message). In some cases it was found that there was an attempt to backup the transaction log of the Master database which is not possible.
Another reason for this problem could be that a domain controller for the specified domain was not available so the SQL agent could not verify the user id configured for that job.
We had the same error on a clustered instance of SQL2k on W2k, after the server had been running fine for 3 months. The job was set to run using a specific account, but the error showed the user to be <domain>\Administrator. Restarting the SQLAgent service solved the problem, and the job ran using the correct account again.
This event can also be caused if account used to create the job no longer exsists. Logon using a valid account and re-create the job in question.
I was able to get the scheduled task running again by creating the task with an admin account local to the SQL server.
I found a solution for this event after going in a lot of circles. It happened when the backup agent tries to set the master database into single user mode (which isn't possible), you can stop this error and backup failures by disabling the Attempt to repair any minor problems option.
To disable the option, perform these steps in the SQL Server Enterprise Manager:
1. Right-click and choose Properties on the Database Maintenance plan.
2. Click the Integrity tab.
3. Clear the Attempt to repair any minor problems check box under Check Database Integrity.