Using Sybase Central, we created a ‘Maintenace Plan’ for our Sevoy project’s database whereby automated back-ups are perfromed on a periodic basis. The ‘Maintenance Plan’ was created using the built-in ‘Maintenance Plan Wizard’ in Sybase Central (no coding of our own). It has run successfully in the past but, lately, has been failing with the following error message:
Validation started on 2008-03-10 at 11:18:30.765 Validating database pages The maintenance plan has ended because of the following error:
Run time SQL error – Database validation failed for database file “C:\Program Files\Servoy\database\ProjectData.db”
SQLSTATE: 40000
SQLCODE: -300
Sybase error code 13664
Probable cause
An internal database error occurred. If it can be reproduced, it should be reported to iAnywhere. You may be able to work around this problem by modifying the associated query statement.
And, specifically, it is complaining about one of the tables in ‘ProjectData.db’ called ‘Contacts’ (table cannot be validated). My assumption is that a table validation failure is indicative of a corrupted table…please correct me, if I am wrong. There is no apparent problem using this table in our Sevoy solution.
Can anyone tell me how to go about fixing this problem? We come from a Visual FoxPro background and do not yet have enough experience with SQL to understand the inner workings.
The validation error can indicate that your database is corrupt, and in this case it looks like it’s your table ‘Contacts’.
To confirm that the table is corrupt, try validating it: either through Sybase Central (connect to db, open Tables folder, right-click and select Validate from popup menu) or by executing the SQL statement VALIDATE TABLE Contacts.
If you see errors, then you have a couple of options:
Drop all indexes and keys on Contacts and then re-create them. This also includes foreign keys. If you suspect a particular index, you can execute an ALTER INDEX … REBUILD statement to rebuild the corrupted index.
Unload and reload your entire database. Please use the dbunload -u option so that it does not try to use a possibly corrupt index to order the data.
Prior to your response, we used Sybase Central and ran the validation on both the ‘Contacts’ table and index…the index validation was successfull, however, the table validation failed. Since we are still in the development phase of our project, the ‘Contacts’ table contained test data, so we decided to delete the rows (records) to see if the problem was data dependent. We ran the table validation on the ‘empty’ table, and it sill failed. We then copied the ‘empty’ table and successfully ran the validaton on it. Next, we deleted the ‘corrupted’ table replacing it with the ‘copied’ table, repopulated the new table using a previous import program we had written, and all is well.
So, we did not have a chance to use your suggestions but will keep them handy in case this happens again.
Our concern now is for when we deploy our product and this happens to our customers. I suppose reverting to a back-up would be appropriate, but that’s the rub. We became aware of this problem when the maintenance plan set in Sybase Central to automatically back-up our database started to fail due to the table validation failure. When accessing this same table in Servoy, there was no indication that the table was corrupt as you could add/delete/modify with no problems!
kwpsd:
Our concern now is for when we deploy our product and this happens to our customers. I suppose reverting to a back-up would be appropriate, but that’s the rub. We became aware of this problem when the maintenance plan set in Sybase Central to automatically back-up our database started to fail due to the table validation failure. When accessing this same table in Servoy, there was no indication that the table was corrupt as you could add/delete/modify with no problems!
Yes, that is a very valid concern. What you can do is build the validation check right into your app, either through the VALIDATE statement, or by invoking the system stored procedure sa_validate. Based on the result, you can then take the appropriate and corrective action. For more information about this stored procedure, please have a look at the docs. You can access them online here: http://sybooks.sybase.com/nav/detail.do?docset=1333.