failed maintenance plan [SQLSTATE 42000] (Error 22029)

We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message:
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
This is the only error message to be found in any of the logs. I've checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can't find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem.
Does anyone have any ideas on how to de-bug this error?
regards
Stuart Ainsley

I'm having the same problem. I reviewed the history but it doesn't tell me anymore than you already have, nor does the Event Log and the SQL Error Log. Have you been able to resolve your problem?
Sidney


------------
Stuart Ainsley at 4/9/01 9:23:41 PM

We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message:
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
This is the only error message to be found in any of the logs. I've checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can't find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem.
Does anyone have any ideas on how to de-bug this error?
regards
Stuart Ainsley

I had the same problem with maint plans a few weeks ago after upgrading to sql 2K. What was so confusing is that the job would usually succeed, and it would not fail consistently(ie, at the same time every day). I went through the bad jobs, opened up each individual step, and changed the "run as" parameter from "self" to my domain account which runs sql(even though the entire job was setup to run as this user). I don't know why, but this seems to have worked for me. HTH

------------
Sidney Ives at 4/30/01 2:41:16 PM

I'm having the same problem. I reviewed the history but it doesn't tell me anymore than you already have, nor does the Event Log and the SQL Error Log. Have you been able to resolve your problem?
Sidney


------------
Stuart Ainsley at 4/9/01 9:23:41 PM

We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message:
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
This is the only error message to be found in any of the logs. I've checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can't find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem.
Does anyone have any ideas on how to de-bug this error?
regards
Stuart Ainsley

I've resolved my issue with this problem. My maint. plan was failing with essentially the same problem. I stopped backing up my trans log with my database. Once I did that, my backup plan was successful. I didn't need a separate trans log backup on the databases I was backing up.

There were two things that led me to this conclusion. First, I ran the query below and reviewed the messages for the failed backups. I noticed that all of them were on the trans log and not the database:

select database_name, activity, start_time, message
from msdb..sysdbmaintplan_history
where error_number = 0 <-- this line optional

Secondly, I read &#39;Transaction Log Backups&#39; in BOL. It indicated to me that I only needed the trans log backup if I was attempting to recover up to a point in time after the db backup was taken. If I have to backup the trans. log, I intend to do it in a separate maint. plan to begin outside of the db backup.

I hope this makes sense.
Sidney


------------
Stuart Ainsley at 4/9/01 9:23:41 PM

We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message:
&#34;sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.&#34;
This is the only error message to be found in any of the logs. I&#39;ve checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can&#39;t find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem.
Does anyone have any ideas on how to de-bug this error?
regards
Stuart Ainsley

HI All,

I have came across the same case my maintanence also failed with error [SQLSTATE 42000] (Error 22029).

Upon my investigation i found that the drive is running out of space where the backup is taking place.I Have released space and re ran the maintainence plan and it is successful.Hope it might work for you too.

Thanks,
Sk.Mahamood
HP India
mahamood351@gmail.com