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

Question

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

Cary P. Thrasher

Mr or Mrs. 500

Points: 559
More actions
July 28, 2004 at 12:25 pm

#152247

At my Company, I set up a Maintenence Plan to back up one of our Databases (quite large) prior to the monthend processing. This was to ensure if data needed prior to month end load, data would be avaialble. I can run this myself and it works fine, but as a scheduled job it received this error. Does anyone know the answer to this. Additional info is that I set the plan to delete any backup old than three months, which hasn't been reached yet.
Error received -
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Diarmuid Hurley SSC Veteran Points: 237 More actions · Answer 1

I've had the very same error occur in the last couple of weeks - I spent some time on the Microsoft Website (Uncle Bill's Domain) and it seems that it relates to the ability of the server to take the database into single-user mode (which it allegedly needs to perform some maintenance tasks (integrity checks in my case)). On my SQL box, it seems that a BackupExec job runs under the NT System Account on the SQL's local OS - this is preventing single-user mode. We are currently looking to kill this BackupExec job for a couple of hours on a Sunday so that the integrity checks can be run. I hope this helps,

Diarmuid.

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 2

good luck! I've had an open issue with MS for over a month now, trying to get this figured out. And I'm only dealing with a one user local MSDE setup. What I can tell you from my experience, is that if I configure the MSSQLSERVER service to run under a Domain\User account that has admin priveledges to the machine, everything works fine. When I put MSSQLSERVER back to Local System, it fails every time, regardless of who initiates the job. The same configuration (Account = Local System) works wonderfully for the other 95% of my users.

In short, the workaround for me was to set the MSSQLSERVER service to run under the user's WinNT Domain\User account. Be warned though, that changing the users password may have detrimental consequences. I haven't tested that.

If I ever get my schedule to match with my helpful MS engineers' and figure out the problem, I'll let you know.

cheetah2399 Mr or Mrs. 500 Points: 567 More actions · Answer 3

Have you checked the maint plan history to see where the failure occured and what may have been successful? I have found that it is very helpful on telling you what went wrong, which for me was usually a wrong setting. Try this article to see if it will help.

http://support.microsoft.com/default.aspx?scid=kb;en-us;288577

sivaprasad SSCertifiable Points: 6183 More actions · Answer 4

I also faced this problem when i schedule a database optimization jon on SQL 7.0 with Sp2 box. Clueless about what cause the problem.

- S Sivaprasad

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Diarmuid Hurley SSC Veteran Points: 237 More actions · Answer 5

Strangely enough - I have since deleted the job, re-created it (exactly the same tasks as before) and all is now running well.

Diarmuid

Mohammad Sufian Ten Centuries Points: 1292 More actions · Answer 6

Some days back, me too had the same error in my plan, and on my standby server i deleted some old backups, and then again restarted my job..it worked fine then..i believe it was due to lack of space but the error thrown is misleading..

jdalonzo SSCrazy Points: 2232 More actions · Answer 7

I'm having the same problem during the optimization step of one on the db on a server. The server has 300 GB free space and nothing else is running at the time. All of the other db on this server have the same plans (run as the same user, backup to the same directory,) but they all work fine. Any ideas what I'm missing? Thanks.

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 8

fyi. the resolution to my error was to register SQLDMO.DLL in C:\Program Files\Microsoft SQL Server\80\Tools\Binn. Apparently, due to in improper installation, the dll was not registered for all users of the system.

sivaprasad SSCertifiable Points: 6183 More actions · Answer 9

Have a look on this link.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q288577

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 10

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!!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

John L. Lutz Valued Member Points: 73 More actions · Answer 11

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 SSC Veteran Points: 285 More actions · Answer 12

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 SSC Veteran Points: 225 More actions · Answer 13

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 SSC Rookie Points: 49 More actions · Answer 14

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