SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.


sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

Author
Message
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4671 Visits: 715

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/
John L. Lutz
John L. Lutz
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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?

aria-231041
aria-231041
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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


sqlJunkies2
sqlJunkies2
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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


Paolo Marini-276271
Paolo Marini-276271
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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


1treehill
1treehill
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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]



Ramangupta
Ramangupta
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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.





Wagner Bertuol Casagrande
Wagner Bertuol Casagrande
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.


A Plockyn
A Plockyn
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 92
__________________________________________________________
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
jgacen
jgacen
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search