james_j_hedges
asked on
SQL Server 2000 sqlmaint.exe maintenance plan execution error
I am experiencing a problem with my maintenance plans running under SQL Server 2000 running on Windows 2000. I suspect that the error (below) is occurring due to permissions problems with the user executing the maintenance plan. What permissions does the executing user need to hold and where do I verify this and change the details if necessary?
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The Step failed.
Executed as user: NT Authority\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The Step failed.
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The Step failed.
Executed as user: NT Authority\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The Step failed.
ASKER
Upon further investigation, it looks more like this is a generic error that is the result of one or more parts of that particular component of the maintenance plan failing. For example, not being able to perform optimisation jobs on databases that are not in single-user mode. In this instance, I suspect that this had nothing to do with permissions and more to do with SQL Server 2000's lack of detailed error reporting.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sometimes when getting this error, a restart of MSSQL (or Server reboot) helps... (Of course make sure that MSSQL Agent is configured properly and started)
Also it's generally better to start MSSQL using a Domain User Account (also Local Administrator on the Database Server) as this generally give more "flexibility" over what you can do especially if you need that server to access Server Resources...
Jerry