DB Maintenance Plan is Failing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DB Maintenance Plan is Failing

Author

Topic

Rita Bhatnagar
Posting Yak Master

172 Posts

Posted - 2002-02-19 : 10:38:48

I have a db Maintenance plan that does full and
transaction log backup for the database.It was
successfull for a long time but the transaction log
backup started failing with the following error:

Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
failed. [SQLSTATE 42000] (Error 22029). The step
failed.

Full database backup is fine.Does Somebody Knows the
reason?
Thanks.
Rita

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-02-19 : 10:45:41

Check out this article:
[url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;q288577[/url]

Rita Bhatnagar
Posting Yak Master

172 Posts

Posted - 2002-02-19 : 11:23:02

Thanks.
I have 8 database maintenance plans but i could see history for only one plan.All other plans are working except this. Still i can't see the history for all.when i select maintenance plans from the combo box,I see only one maintenace plan.What am i doing wrong?
Rita

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-19 : 12:15:24

Have a look around various sites like this one and see how many posts are about people having problems with maintenance plans; then decide if you really want to use them.
It's easy to code the functionality in a stored procedure - you will learn a lot more about what it is doing and what is available and have control over what is executed.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Rita Bhatnagar
Posting Yak Master

172 Posts

Posted - 2002-02-19 : 13:05:21

Thanks.
I'll try to do that.Do i need to use references other than bol or that would be sufficient?
rita

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-02-20 : 02:07:19

I had just this problem myself earlier today.

First, check the individual step history for the plan's job and see if it's really failing altogether, or if just one step is.

Next, find the plan in msdb..sysdbmaintplans and get its plan_id. Now, look up its history in msdb..sysdbmaintplan_history based on that plan_id and succeeded=0, and look at the "message" column.

For instance, if the plan_id is 0C80A886-25C5-11D6-94C1-00D0B7A7816A, you want:

select * from sysmaintplan_history where plan_id='0C80A886-25C5-11D6-94C1-00D0B7A7816A' and succeeded=0

In my case, I was trying to a transaction log backup of master, which is apparently not allowed. Every other step of the plan was fine, but the plan was always being recorded as "failed."

Hope that helps
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources