sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
-
I got this error as well. The job has worked fine for over a year now but it appeared in my log 3 weeks ago.
In my case it seems to be a diskspace problem (its full and there no place for a bigger log file right now
so I just want to know if this message( sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed. )
also applies to a diskspace problem.
Thanks.
Oh, the job fails during an optimalization step
[Edit]Never mind, followed the link on the previous page and the above question was answered
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 9002: [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'PersonHandling' is full. Back up the transaction log for the database to free up some log space.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
[/edit]
-
if you contiue to get this error. you should check your DB recovery model. if it is set to simple, then you will get following error:
Executed as user: <user>. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Set your DB recover model to FULL.
I hope this helps in additon all other comments posted.
-
Folks, I really want to thank you all for the tips. They really solved a big boring problem. I hope be usefull sometime.
Thanks.
-
__________________________________________________________
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)
__________________________________________________________
Best Regards,Alain
-
Microsoft has confirmed this to be a problem in SQL Server 2000.
http://support.microsoft.com/?scid=kb%3Ben-us%3B290622&x=9&y=8
-
I'm getting this error message on the backup job that ran happily for over two months. It's backup only on a bunch of databases, no integrity checks or optimization, no transaction log backup.
Here's the strange part - there is no record of the job in Maintenance Plan History. Not with Fail, not with Succeed, it just is not there. Running the job at a different time did not help.
Another backup job to the same location that runs under the same user succeeded, so it's not rights. A manual backup of a few databases succeeded as well, so it's not the disk space.
Any ideas?
-
Hi Guys,
What worked for me. Right click the sql server agent and look at the following tabs; general and connection. The general tab contains the information for starting up the service. I have used "This Account" and gave my sysadmin user this role for starting up the service. I now have gone to the connection tab and selected the option of sql server authentication and used my sa username and password.
This worked for me and i now can execute my jobs.
Regards,
Bartus de Paiva
-
Obviously the account under which the maintenance plan is executed has not enough permissions. See accounts in SQLAgent.-
-
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
SQL 2000:
Go to Management, SQL Server Agent, Jobs, next to your job it will display Maintenance Plan name,
i.e. Transaction Log Backup Job for DB Maintenance Plan 'Reporting Backups'
Go to Database Maintenance Plans, right-click the maintenance plan you want to view - 'Reporting Backups', Select Maintenance Plan History. You will notice that a few jobs failed while others were successful, thus job status displays failed. Check the job/s that failed (the job may have failed on database1, but passed for the other 9 databases).
SQL 2005:
Go to SQL Server Agent, Jobs, your job will display the same name as Maintenance Plan name,
i.e. 'Reporting Backups'
Go to Database Maintenance Plans, right-click the maintenance plan you want to view - 'Reporting Backups', Select View History. You will notice that a few jobs failed while others were successful,
thus job status displays failed. Check the job/s that failed (the job may have failed on database1, but passed for the other 9 databases).
Regards
Kevin
-
I found that if you have multiple database per maintenance plan, it cause the jobs to failed when there are changes to one of the databases selected on the maintenance plans. Check if you have multiple databases selected, it is wiser to separate each database with its own maintenance plan and its easier for trouble shooting.
Harry
-
Sometimes its best to create separate jobs for:
backups of databases,
backups of logs,
db integrity check,
update of statistics
With integrity check & update of statistics, it depends how big your databases are,
if over 20GB they might take long so schedule them over weekends.
Use maintenance plan to create backups, then script them (view T-SQL),
then add the script to a normal SQL job. This way you can add as may databases as you want to a
specific job as you want (lets say the sales department has 5 databases-add to salesjob, lets say
the marketing dept has 3 databases-add to marketjob). This way it easier to view job history and check what failed.
-
"regsvr32 sqldmo.dll" solved the problem for me too. Thanks for the tip
-
I've had the same problem with a maintenance plan.
-All databases on Simple
-No Backup of transaction logs
Error was because the databases were manually selected in the maintenance plan, and one of the databases was removed.
They didn't remove the database from the maintenance plan.
It showed at the bottom of the list.
After removing the database everything was fine again..
-
Thanks very much again! Yet again this forum has been very useful.
Regards,
Vivek
Vivek Shukla - MCTS SQL Server 2008
-
regsvr32 sqldmo.dll worked for me to
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply