Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed. Expand / Collapse
Author
Message
Posted Monday, July 18, 2005 12:11 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697

I would personally never use a maint plan - always preferring to write the jobs in clear tsql - beware that sysmaint.exe works as an external process to sql server. Anyway in the maint plan setup there is an option to fix errors on checkdb - don't choose this as this dbcc command rightly needs exclusive single user mode to run - if you checked this box clear it and your maint plan should run.

Generally you need dbo rights to work on integrity / optimisation jobs ( and the agent service needs rights too ) Servers normally have local admin rights ( or close to ) for the server service account and agent service - this allows jobs to run and dbcc commands to run without error.  Although it's not ideal to have your server as the local admin of its box you'll find most jobs/activities work much better!!



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #202122
Posted Tuesday, July 19, 2005 10:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 8, 2006 3:25 PM
Points: 1, Visits: 1
I am having a similar problem, but none of the posted suggestions seem to apply. Here's the scoop. I have set up two maintenance plans for a database. One does just the log backups and the other does everything else, including data backups. Together, these created several SQL jobs. All jobs run fine (including the log backup) except for the data backup - so it doesn't appear to be a problem with xp_sqlmaint. If I copy the data backup task and paste it to Query Analyzer, it runs fine - so it doesn't appear to be space problems or any problems associated with any of the switches in the command line.

Any ideas?
Post #202444
Posted Wednesday, September 14, 2005 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 1, 2006 9:56 AM
Points: 9, Visits: 1

Did anyone find a solution.  I am getting this error, using log shipping, but in the log shipping maintenance job, the backups were successful.  The error is coming when the standby server tries to restore the transaction logs.  I have no idea what this error means

 

Thanks,

Aria

Post #219747
Posted Monday, October 24, 2005 12:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 14, 2009 5:07 PM
Points: 15, Visits: 41

I've this error on one of my full db backup job. Indeed the error msg didn't help much but I found it in ntevent viewer that my harddisk is full. So just wonder did you guys check the NTEvent log

James

Post #231794
Posted Thursday, November 10, 2005 3:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2008 2:23 AM
Points: 1, Visits: 2

I was having the same problem when I found this article:
http://support.microsoft.com/?kbid=303229

I am a backup-ignorant but it seems that when the recovery model is set to "simple", then it is not possible to backup the log db (probably because it would be useless). This is why the related scheduled job was failing while the same job for the db (and not the log) was working fine.

I think that the Enterprise Manager should check the recovery model used for the specific database and prevent users to try to backup the log while it is not possible.

Or, perhaps, I should read some books before playing with backups...

Thanks,
Paolo

Post #236242
Posted Sunday, November 13, 2005 6:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 1, 2007 3:53 AM
Points: 9, Visits: 1
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]



Post #236757
Posted Tuesday, February 7, 2006 2:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 28, 2014 8:42 AM
Points: 5, Visits: 191

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.




Post #256555
Posted Tuesday, December 12, 2006 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 5, 2007 9:43 AM
Points: 1, Visits: 1

Folks, I really want to thank you all for the tips. They really solved a big boring problem. I hope be usefull sometime.

Thanks.

Post #329821
Posted Friday, November 16, 2007 2:59 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 3:21 AM
Points: 16, Visits: 52
__________________________________________________________
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
Post #422951
Posted Thursday, December 13, 2007 2:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 28, 2008 9:08 AM
Points: 1, Visits: 3
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
Post #433071
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse