database maintenance error

[DanC]

Hi all, I'm getting an error when trying to execute a database maintenance plan. I'm not doing any backup functions, just the optimizations and integrity check portion. These jobs used to work, but now they are not. The error I get in the text report is:

Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: 28000)] Error 18456: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\SYSTEM'.

The error is listed in job history as:

Executed as user: ****\Administrator. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

What do I need to do to fix this problem? I can't seem to find the answer anywhere.

Thanks in advance for the help.

 

[tlbroadbent]

Check the JOB owner. You may need to change it to SA.

Is the SQLAgent startup account a local administrator or a local system account? What account is SQL Server running under? Have the accounts been changed recently?

If you haven't viewed Job details yet, open Enterprise Manager. Expand Management | SQL Agent | Jobs. Right click the job that failed and select View Job History. Select the "View step details" option in the top right of the screen. Then select the step shown as failing and read the error message showing in the bottom part of the window. This may give more information about the reason for failure.

Check

http://support.microsoft.com/default.aspx?kbid=288577.

As a last resort, you may need to recreate the maintenance job. Sometimes, it is faster to do that than debugging the failing job. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[DanC]

thanks for the quick reply Terry. The only thing that shows up in the job history is : Executed as user: DD&C\Administrator. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

I just tried changing the ownership to sa and still got the same error. I also recreated it and still get the error. It looks like the sqlagent account has been changed. It would appear that it's being started under a domain administrator account now. Does the account need to be a local admin account in order to run these?

 

[tlbroadbent]

I recommend running SQLAgent using a Domain Account that is a local administrator on the Server. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.