September 21, 2003 at 6:48 am
I have SQL Server 2000 (SP3a) personal edition set up on a Windows XP professional machine.
For any process I attempt to set up in a Maintenance plan, I always get the above message. I am selecting to write the results to logs and to the msdb sysdbmaintplan_hist table, but no information gets posted to these locations. No log gets created. The error is visible in the Job history and in the Windows Application log, but there are no details.
I have looked at the info in the Microsoft knowledge base, but have not found it to hit on my situation. I tried using the local system account, and have switched to the administrator account for this local machine.
When I had SQL Server 2000 personal edition setup on a Windows 2000 professional machine, Maintenance plans worked fine. I am not sure what is going on. Ideas really appreciated. Thanks.
View maintenance plan history shows now information, even thought the plan is set to retain the history.
I've even uninstalled SQL and reinstalled with Service Pack 3a on this machine.
I would appreciate any insight. Thanks.
September 21, 2003 at 8:55 am
This is a generic error, see if you can find the exact error by looking at the details on the individual job steps. This sometimes has to do with security but again, is mostly a generic error. If you are trying to do log backups, make sure the DB's are not in simple recovery mode.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 21, 2003 at 11:22 am
Thanks for your response.
But there is no more detailed information than just this error in job history or in the Windows Application log. Is there something I could try to enable more details, in running the process through query analyzer or through a command prompt?
September 21, 2003 at 4:06 pm
You could do that. If you right click the job (not the maintenance plan) and click view job history it will open a new window. From there you can check the box entitled see job details, then drill down to where the actual error occurred.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 22, 2003 at 12:55 am
I agree with Ray...If you see the Job History with the Individual step details it will usually give you the exact step where the error occured with a some description.
Cheers!
Arvind
Arvind
September 22, 2003 at 12:58 am
On second thought you could also try running dbcc checkdb individually on your databases in query
analyzer and see if you get any error report.
Cheers!
Arvind
Arvind
September 22, 2003 at 1:01 am
September 22, 2003 at 4:37 am
Thanks for your posts.
This is the only information listed in the step detail in job history: Executed as user: D9992011\Administrator. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed. If I change the owner of the job to sa, I get the same error. (Also, the databases I am trying to backup are not simple, either.)
I am stumped I'll try DBCC CHECKDB on my databases.
September 23, 2003 at 12:55 pm
DBCC CheckDB reports no errors on the databases I am trying to back up.
Perhaps it a security issue of some kind.
Not sure where to go except rebuild the machine.
July 20, 2005 at 2:52 am
I found the same errors and discovered that users were connected to the db while it was trying to restore. You can check out the logshipping monitor and find out what exactly the problem is by right clicking on the logshipping pair.
July 22, 2005 at 11:55 am
I had the same error recently. Diagnostic messages were written to the maintenance plan report but not to the maintenance plan history. As you say "no logs were created" I guess you haven't set up a report? Either alter your maintenance plan to produce one or cut and paste the xp_sqlmaint commands from your job into query analyser and run them there. The "report" will go to your query analyser output window.
Also worth checking the windows event logs. Read on for why.
The detail I'm about to give may not be relevant to your case (as others have said you are seeing a generic error) but shows what is possible.
My job was failing with: "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed"
The maint report said: "Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: IM002)] Error 0: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
I thought this was weird because nothing had changed. I found these messages in the Windows event log. They weren't from SQL but prompted me to reboot which cured things: "Server Agents Warning Remote Insight 3331 N/A "Component: Remote Insight Agent. Error: Could not read the registry sub-key: ""SOFTWARE\Compaq Insight Agent"". Cause: This error can be caused by a corrupt registry or a low memory condition. Rebooting the server may correct this error."
November 4, 2005 at 9:52 pm
Hi,
I have changed my job profile from being a project leader to DBA. You could say am an aspiring DBA. I am facing the same problem. I have a query regarding this.
Could this be a security issue as Ray had mentioned earlier? The job is backing up the user databases. I have checked whatever has already been mentioned above. will the owner of the job and owner of the Db being different make any problems? This user does not have the specific databases added into its list of database access. Will this be a problem?
Hope to get a reply ASAP
November 5, 2005 at 6:24 am
Did you change the SQL Server and SQL Agent Account using the Services Manager? If so, you will encounter a lot of problems.
Change the accounts using Enterprise Manager, restart the computer, then change the accounts again, restart and finally retry the maintenance plans.
SQL = Scarcely Qualifies as a Language
November 16, 2007 at 2:38 am
__________________________________________________________
Looking at the 100's of posts out there, I do believe it is a generic errror message that DOES NOT SAY ANYTHING.
In our case it was the INTEGRITY checks of the maintenance plan that failed. (SQL 2000 SP4 on WINDOWS 2003 SERVER)
Our solution was to ensure NO OTHER users (Query Analyzer: sp_who2) are/were
using the database (DBName) you are trying to optimise (as the task
tries to do an ALTER DATABASE! which needs exclusive database access - see below).
This would explain why some people found that:
- REBOOTING the server (or a power cut)
- RESTARTING SQL Server service
- making the database(s) SINGLE USER
- waiting long enough (or just luck)
also solve the problem because all that did, was to release all other users/requests on the database(s)
Hope this helps you or others
Alain
__________________________________________________________
Background:
We used a standard maintenance plan which produced:
- DB Backup Job
- Integrity Checks Job
- Optimizations Job
and the only one to fail was the "Integrity Checks" (right-click "View
job history" + tick "[_] Show step details" + Step 1) with that:
>>> Executed as user: xxyyzz. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed. <<<
Then we used Query Analyzer to execute the command it contained:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
9DEB66C0-FC7F-4047-88E3-8A6A64BB74FD -WriteHistory -CkDBRepair '
--> Look at the "GRIDS" tab (not the "Messages" tab) for the execution/errors
__________________________________________________________
In details:
Integrity Checks Job for DB Maintenance Plan 'MyPlanName' Properties
under the tab: Steps
Step 1 -> Edit...
Step Name: Step 1
Type: Transact-SQL Script (TSQL)
Database: master
Command: EXECUTE master.dbo.xp_sqlmaint N'-PlanID
9DEB66C0-FC7F-4047-88E3-8A6A64BB74FD -WriteHistory -CkDBRepair '
__________________________________________________________
Above command in Query Analyzer:
FAILED:
__________________________________________________________
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039
Copyright (C) Microsoft Corporation, 1995 - 1998
Logged on to SQL Server 'MyServerName'
as 'MyServerName\SQL_Server_Service_Account' (trusted)
Starting maintenance plan 'MyPlanName' on 15/11/2007 15:36:03
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
SQL Server Driver][SQL Server]Database state cannot be changed while
other users are using the database 'MyDatabaseName'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
failed.
[1] Database MyDatabaseName: Check Data and Index Linkage...
[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.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'MyPlanName' on 15/11/2007 15:36:03
SQLMAINT.EXE Process Exit Code: 1 (Failed)
__________________________________________________________
Same command in Query Analyzer (having removed all other users sp_who2):
SUCCESSFUL:
__________________________________________________________
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039
Copyright (C) Microsoft Corporation, 1995 - 1998
Logged on to SQL Server 'MyServerName'
as 'MyServerName\SQL_Server_Service_Account' (trusted)
Starting maintenance plan 'MyPlanName' on 15/11/2007 12:04:53
[1] Database MyDatabaseName: Check Data and Index Linkage...
** Execution Time: 0 hrs, 3 mins, 7 secs **
End of maintenance plan 'MyPlanName' on 15/11/2007 12:08:00
SQLMAINT.EXE Process Exit Code: 0 (Success)
__________________________________________________________
Alain
November 29, 2007 at 11:37 am
My vote goes to the job cleanup. If Tivoli or Windows Backup is hitting a file when your job tries to delete it.......I think you can get the 42000. See if backups or backup reports aren't being trimmed/deleted.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply