Question Re: The database could not be validated. in Technology Q&A

The database could not be validated.

huber1 — Sun, 27 Jan 2013 16:43:49 GMT

When running VALIDATE DATABASE from Sybase Central (or in Interactive SQL as command) I get the following messages:

Page 0000037d of database file "E:\\SQLAnywhere12\\databases\\Hades.db" references a physical index (1295, 1) that doesn't exist
Orphaned page (00000695) found in database file "E:\\SQLAnywhere12\\databases\\Hades.db"
Page 00000df8 of database file "E:\\SQLAnywhere12\\databases\\Hades.db" references a physical index (1295, 1) that doesn't exist
Orphaned page (00000ecc) found in database file "E:\\SQLAnywhere12\\databases\\Hades.db"
Page 00000efa of database file "E:\\SQLAnywhere12\\databases\\Hades.db" references a physical index (1295, 1) that doesn't exist
Orphaned page (00000efc) found in database file "E:\\SQLAnywhere12\\databases\\Hades.db"
java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Run time SQL error -- Database validation failed for database file "E:\\SQLAnywhere12\\databases\\Hades.db"
The database 'Hades' could not be validated.

Any hint how to cure that problem?

Thanks, Robert

Re: The database could not be validated.

Breck_Carter — Sun, 27 Jan 2013 17:01:44 GMT

Make sure there are no other users connected when you run the validation.

Re: The database could not be validated.

VolkerBarth — Sun, 27 Jan 2013 17:02:21 GMT

Is the database used by other connections (and are these possibly doing transactions)? - If so, note that validation should be done on a "quite" database, otherwise false positives may be shown.

Re: The database could not be validated.

reimer_pods — Mon, 28 Jan 2013 03:46:18 GMT

@Volker: I assume you meant to write "quiet" 🙂

Re: The database could not be validated.

huber1 — Mon, 28 Jan 2013 07:49:26 GMT

@Breck: There are no other users connected when I run the validation. I should also mention that I validated all tables and indexes, and got no errors reported. I am wondering what a physical index is? What other indexes exist?

Re: The database could not be validated.

Breck_Carter — Mon, 28 Jan 2013 09:06:50 GMT

A physical index is the real thing. If two logical indexes (the kind you create with CREATE INDEX, PRIMARY KEY, UNIQUE constraint, etc) refer to the same columns in the same order then they will implemented by the same single physical index. Otherwise, no difference.

Re: The database could not be validated.

Breck_Carter — Mon, 28 Jan 2013 09:07:37 GMT

Time to call tech support, or recover from a backup.

Re: The database could not be validated.

jason_noack — Mon, 28 Jan 2013 13:26:47 GMT

Hello Robert,

I believe that the errors are likely real and likely caused by an old bug in the software. The cause of the errors is that there are some pages in the database file that seem to still be allocated to an object (index in this case) that no longer exists. These pages haven't been freed and thereby cannot be re-used for another purpose. They are essentially leaked pages in the database file. They don't actually present a problem other than the validation errors that they cause. Rebuilding the database (unloading and reloading) will solve the issue for you. I'd recommend using a more recent build to prevent this from occurring again as I do recall some issues that could cause this behavior.

Jason Noack

Re: The database could not be validated.

huber1 — Wed, 30 Jan 2013 08:21:08 GMT

Jason,

Thanks a lot for your answer, I am relieved to hear that. At a good moment, I will do what you suggest.

Regards, Robert

Re: The database could not be validated.

huber1 — Tue, 24 Sep 2013 10:20:57 GMT

I am still a bit uncertain what to understand by Rebuilding (unload and reload) the database. I did it with Sybase Central, menu Tool > SQL Anywhere 12 > Unload Database then followed the on-screen messages to the screen Connect ot the Database which I unchecked and that created a database file and a log file to the desktop. Which I put after shutting down the database into the correct folder. Now validation was ok. Is that one of the correct ways to rebuild a database? I assume so, but would be glad to hear it's correct.

Robert Huber

Re: The database could not be validated.

justin_willey — Tue, 24 Sep 2013 10:57:19 GMT

Yes - Unloading and Reloading are the processes need to Rebuild a database. You can also do the same thing with command line tools - see dbunload.exe

Re: The database could not be validated.

jason_noack — Tue, 24 Sep 2013 11:09:13 GMT

Yes Robert that is precisely the mechanism I was referring to.

Good luck to you!

Re: The database could not be validated.

huber1 — Wed, 25 Sep 2013 04:23:22 GMT

Did rebuild the productive database – validates correctly (again 🙂 Thanks!