I have a 40gb database (SQL Anywhere 17) that I am trying to backup and have begun to get errors when validating a certain table. I am using a maintenance plan that runs when the database should be at the lowest level of use (3:45 am) but I can't get it to validate and do the backup. I have added some prevalidation commands to rebuild the indexes on the table that fails validation and they run successfully. It continually fails with this message:

The maintenance plan has ended because of the following error: Run time SQL error -- Validation of table "TRAV_MODS" has failed SQLSTATE: 40000 SQLCODE: -300 Maintenance plan Backup ZZZZZZ for YYYYY on XXXXXXX finished on 2020-01-15 at 03:50:36.470

Any ideas as to what I might try?

asked 15 Jan, 09:25

wraventjr's gravatar image

wraventjr
41127
accept rate: 50%

edited 15 Jan, 10:15

Volker%20Barth's gravatar image

Volker Barth
36.1k342501749

By the way, I have tried Full Image backups and Full Archive backups and neither will work. Both fail at the same place.

(15 Jan, 09:37) wraventjr
Replies hidden
1

Presumably you have an older backup that didn't fail the validation. Start that one and apply the subsequent log backups to bring it up to date.

Tip: Backups are easy, restores are hard... restores are especially hard if the backups have never been tested.

Tip: When working on a restore, do not overwrite ANY files, even if they are faulty... if necessary, make file copies of everything and put them somewhere safe.

(15 Jan, 09:53) Breck Carter
Replies hidden

FWIW, you might also try to just validate the according table with VALIDATE TABLE statements. In some cases, it might be sufficient to rebuild according indexes.

(15 Jan, 10:14) Volker Barth

Well, if the database is not valid as a whole, why would you want to backup that?

(15 Jan, 10:14) Volker Barth

Volker, I'm ignorant of how the delivered "backup plan" feature works. Does it run the validation on the active database, or does it run the validate on the backup copy with no other connections?

(15 Jan, 11:26) Breck Carter

At this point I have no valid backups of this database. I am pretty much screwed if this system fails. My only hope is a third party that is backing up changed files every 15 minutes. I hope I don't have to test this out until I get this fixed.

I have tried validating the table the backup is failing on and I am finding it will validate 4 out of 5 times and the 5th time failure is caused by an index row count mismatch between an index and the table. I suppose I could delete the index and see how the performance is affected.

This column that the failing index is on has a lot of <blank> rows. Could that be causing the count mismatch?

(15 Jan, 11:28) wraventjr
Replies hidden

Sorry, not blank rows but blank entries in the column.

(15 Jan, 11:30) wraventjr

AFAIK, the builtin maintenance plan facility creates an event that does one or more of

  • validating the running database and
  • backing up the database

so it's not a validation of a copy.

AFAIK by default it prevents new connections (via CALL SA_SERVER_OPTION('ConnsDisabled', 'Yes')), I don't know about current users.

(15 Jan, 11:54) Volker Barth

At this point I have no valid backups of this database.

So what backups do you have available at all?

And can you shutdown that database for further validation (and possibly rebuilding), or is it still used by actual users?

(15 Jan, 11:59) Volker Barth

I cannot shut down the db as it is in use by users. I may be able to schedule a shutdown over a certain time period during a weekend.

(15 Jan, 13:03) wraventjr

> validating the running database

Doesn't that guarantee failure?

The dbvalid docs say you should "ensure that no changes are made the database while it is being validated".

(15 Jan, 13:33) Breck Carter
1

Are you seeing any errors other than errors reported by the validation process?

If the answer is no, perhaps you should stop doing validation as part of the "backup plan", and do validation separately on the resulting (idle) backup copy. If the backup copy passes validation, then you know (by definition) the original database must be valid since the backup process does not fix errors :)

(15 Jan, 13:36) Breck Carter

I have deleted the index that was failing validation and will see if the backup works tonight.

(15 Jan, 16:46) wraventjr

If it fails, I would really recommend to separate the tasks of validation and backup, so you get a backup for further analysis even if the validation failed.

(15 Jan, 17:37) Volker Barth

Well, it could lead to spurious validation errors, yes. Personally, I think these plans are a good starting point but should be adapted for real usage... And one should read and understand the generated code...

(15 Jan, 17:39) Volker Barth
1

It is possible for validation errors to be reported if there are changes happening on the database during the validation process - since this is a live database, this is certainly a possibility. This behaviour is documented. The "it will validate 4 out of 5 times and the 5th time failure is caused by an index row count mismatch between an index and the table." is consistent with changes happening to the database while the validation is being run. If you must validate a live database, you may need to consider the VALIDATE options WITH DATA LOCK or WITH SNAPSHOT. As others have noted, have you validated a copy of the backup(s) to see if it has issues. I suspect those will be clean.

(16 Jan, 10:17) Chris Keating

I am hoping to get a "unvalidated" backup tonight. I tried the Express check and it didn't validate, stopping the backup.

For Chris, when I was doing the validation of the database and it failed 1 out of 5 times I was doing on a database that had no activity on it. That made failures very suspect, but you are right about the database having activity on it. I am running the backup when it is least used to see if that will help, although I am not totally sure it is inactive.

(16 Jan, 16:34) wraventjr
More comments hidden
showing 5 of 17 show all flat view

Does the following information help you?

1958942 - Error: "Assertion failed: 123456" / SQLCODE -301 "Internal Database Error" - Handling an Assertion Failure

How to Salvage the database if there is no valid backup and the database starts, but assertion failures occur frequently during operation

permanent link

answered 16 Jan, 02:49

asa's gravatar image

asa
161127
accept rate: 9%

Did you post this to the wrong question?

(16 Jan, 08:50) Breck Carter
2

Just my 2 cents, if you continue to have problems with validation, and dbbackup is having trouble, I would schedule some downtime and do a dbUnload / reload.

(16 Jan, 09:27) Bud Durland MRP

Ok, I was finally able to get a backup without using validation. Now I need to test to see if it will validate before I unload and reload. I do need to schedule some downtime to unload and reload as you suggested. I think I have a grip on what I need to do now. Thanks for all your help.

(17 Jan, 16:16) wraventjr
Replies hidden

> schedule some downtime to unload and reload

For testing purposes, try copying your backup database and running the unload/reload on a different computer; e.g., use a USB drive and an ordinary workstation.

FWIW "40GB" is not big.

(18 Jan, 08:57) Breck Carter

But just when the validation of the copy has failed, right?

(18 Jan, 09:19) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×82
×29

question asked: 15 Jan, 09:25

question was seen: 201 times

last updated: 18 Jan, 09:19