maintenance plan backup validation failures

[Former Member]

Hi. One of our customers is reporting that his maintenance-plan-driven backup will sometimes fail validation. He has the process setup to send him an email when a failure occurs. What causes these failures? Always, subsequent backups are (for a time) fine--report no failures.

[Former Member]

Is there any indication about the reason of the backup fail in the email text?

[VolkerBarth]

...or in the table that stores the last n runs of each maintenance plan (I don't remember the table name currently, something with "maint_..." I guess)?

[Former Member]

Here's one... The maintenance plan has ended because of the following error: Run time SQL error -- Validation of table "schedules" has failed SQLSTATE: 40000 SQLCODE: -300 Here's another... The maintenance plan has ended because of the following error: Run time SQL error -- Validation of table "groups_actual_volume" has failed SQLSTATE: 40000 SQLCODE: -300

[VolkerBarth]

FWIW, the table is called "dbo.maint_plan_report", and the column "report" should contain the full message text, so that should help for later diagnosis.

Besides that, if the server is running a console log (dbsrvX -o), it should contain messages for all validation steps - AFAIK the validation facility always writes its messages there.

[VolkerBarth]

Is the database in active use (i.e. running transactions) during the validation? (IIRC, the maintenance plan runs its validation against the active database, not against a copy of the backup.) - If so, confine the following general warning on validation:

Caution

Validating a table or an entire database should be performed while no connections are making changes to the database; otherwise, errors may be reported indicating some form of database corruption even though no corruption actually exists.

Does a further validation of the same database (i.e. not in the next run) give the same errors?

[former_SQLA_member1694870]

Would the option below be an alternative to avoid the scenario Volker described?

"Disconnect all users (including those connected to Sybase Central) when the maintenance plan runs?"

[VolkerBarth]

Yes, that would surely avoid that situation.

---

FWIW, I have some maintenance plans created with v11.0.1, and I guess that option was not available then. At least the according validation plan does only prevent logins during its run, i.e. only the following option has been set:

"Disallow logins while the maintenance plan is running"

If only this option is set, this would still allow existing connections to make modifications which might lead to the cited false positives.

[former_SQLA_member1694870]

A similar behavior was reported when the maintenance plan starts from a workstation instead of a network server as described in this question.