Bizarre maintenance plan failure (db backup)

[nielsonj1976@yahoo.co.uk]

I am getting a failure on the db backup job of one of my maintenance
plans. It is coming back with the generic error message of,

"sqlmaint.e xe failed. [SQLSTATE 42000] (Error 22029). The step
failed."

I then checked the Database Maintenance Plan History page, but this
shows all the steps having run successfully. If I check the drives for
the actual backup files, they exist and look healthy too!

There is plenty of space on the drives, so it is not that.

I've checked the NT logs and all they say is,

"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan for All User Databases''
(0xC06E15E2A9E1 414087BE19541D1 67861) - Status: Failed - Invoked on:
2005-06-29 21:00:04 - Message: The job failed. The Job was invoked by
Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "

Which doesn't give me any clues.

Since the backups have actually run to success, I am going to take off
the option on the maintenance plan to "Verfiy the integrity of the
backup upon completion". Maybe it is this that is causing problems,
rather than the backup?

Anyone had anything similar?

[Erland Sommarskog]

Re: Bizarre maintenance plan failure (db backup)

(nielsonj1976@y ahoo.co.uk) writes:[color=blue]
> I am getting a failure on the db backup job of one of my maintenance
> plans. It is coming back with the generic error message of,
>
> "sqlmaint.e xe failed. [SQLSTATE 42000] (Error 22029). The step
> failed."
>
> I then checked the Database Maintenance Plan History page, but this
> shows all the steps having run successfully. If I check the drives for
> the actual backup files, they exist and look healthy too!
>
> There is plenty of space on the drives, so it is not that.
>
> I've checked the NT logs and all they say is,
>
> "SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan for All User Databases''
> (0xC06E15E2A9E1 414087BE19541D1 67861) - Status: Failed - Invoked on:
> 2005-06-29 21:00:04 - Message: The job failed. The Job was invoked by
> Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "
>
> Which doesn't give me any clues.[/color]

So did you look at the Job History? Find the backup job, right-click
and select Show History. In this dialog check "Show step details". The
step details usually have some more meat for information.
[color=blue]
> Since the backups have actually run to success, I am going to take off
> the option on the maintenance plan to "Verfiy the integrity of the
> backup upon completion". Maybe it is this that is causing problems,
> rather than the backup?[/color]

Ehum, if the problem is that the backups don't verify, that I would
not like to know that and not sweep it under the carpet.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[nielsonj1976@yahoo.co.uk]

Re: Bizarre maintenance plan failure (db backup)

[color=blue]
> So did you look at the Job History? Find the backup job, right-click
> and select Show History. In this dialog check "Show step details". The
> step details usually have some more meat for information.[/color]

This doesn't really give me any clues either. If I click show step
details, I get it displaying 2 steps,

Step 1: Executed as user: OMNIA\SVC_SQL_E xec. sqlmaint.exe failed.
[SQLSTATE 42000] (Error 22029). The step failed.

Step 0: (Job Outcome) The job failed. The Job was invoked by Schedule
35 (Schedule 1). The last step to run was step 1 (Step 1).
[color=blue][color=green]
> > Since the backups have actually run to success, I am going to take off
> > the option on the maintenance plan to "Verfiy the integrity of the
> > backup upon completion". Maybe it is this that is causing problems,
> > rather than the backup?[/color][/color]
[color=blue]
> Ehum, if the problem is that the backups don't verify, that I would
> not like to know that and not sweep it under the carpet.[/color]

Having checked this morning the backup status is still showing Failed
even with the verify option off. The only reason I thought it *could*
be this is that the backups are fairly large (25gb+) so SQL server
could be having problems verifying with the disk space available?

[Erland Sommarskog]

Re: Bizarre maintenance plan failure (db backup)

nielsonj1976@ya hoo.co.uk (nielsonj1976@y ahoo.co.uk) writes:[color=blue]
> This doesn't really give me any clues either. If I click show step
> details, I get it displaying 2 steps,
>
> Step 1: Executed as user: OMNIA\SVC_SQL_E xec. sqlmaint.exe failed.
> [SQLSTATE 42000] (Error 22029). The step failed.
>
> Step 0: (Job Outcome) The job failed. The Job was invoked by Schedule
> 35 (Schedule 1). The last step to run was step 1 (Step 1).[/color]

I rarely set into maintenance job, so I don't really know where sqlmaint
might write its errors. But looking at the properties of a plan that I've
actually set up, I see that there is a Reporting tab. Have you checked
what's yours, and if that leads to a useful file?
[color=blue][color=green][color=darkred]
>> > Since the backups have actually run to success, I am going to take off
>> > the option on the maintenance plan to "Verfiy the integrity of the
>> > backup upon completion". Maybe it is this that is causing problems,
>> > rather than the backup?[/color][/color]
>[color=green]
>> Ehum, if the problem is that the backups don't verify, that I would
>> not like to know that and not sweep it under the carpet.[/color]
>
> Having checked this morning the backup status is still showing Failed
> even with the verify option off. The only reason I thought it *could*
> be this is that the backups are fairly large (25gb+) so SQL server
> could be having problems verifying with the disk space available?[/color]

I will have to retract what I said above. I recalled a discussion among
my MVP colleagues who are into server administration. The consensus was
that verification of backups as offered by RESTORE VERIFYOLNLY is useless.
The only way to really verify a backup is to restore it. And run DBCC checks
on the restored copy.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[haggul]

Do your maint plans cover more than 1 database? If so check the recovery modes of each - this error seems to pop up if there are mixed modes within 1 plan.