Question Re: Drop corrupt table in Technology Q&A

Drop corrupt table

former_SQLA_member1694892 — Fri, 02 Jul 2021 11:37:19 GMT

We had a hard crash of a database which has left at least one (that we know of) table corrupt. We're not able to validate, query, drop, truncate the table without taking down the database. I am able to query the objects: SELECT sc.* FROM dbo.syscolumns sc INNER JOIN sysobjects so ON sc.id = so.id WHERE so.name = 'fileusertypes'. We want to just kill the table but don't know how at this point. Suggestions? TIA SQL Anywhere 12

Re: Drop corrupt table

chris_keating — Fri, 02 Jul 2021 13:40:56 GMT

Some corruptions may prevent operations to remove the table. I assume that since you are only looking at "dropping" that you have no viable backup that can be used? That would be the easiest approach and should get you to a recovered database vs a data salvage.

In any event, an approach to take in the absence of a backup can be

Unload "schema only" i.e., dbunload -n
Unload "data only" but exclude the corrupt table(s) i.e., dbunload -e <exclude_table_list> -d
(Optional) Attempt to select around the damage using an ASC ordered query on the table and a DESC ordered query on the table unloading each order to a file, i.e.,

unload select * from ... order by ... ASC to <file> append on; unload select * from ... order by ... DESC to <file> append on;
Init new database with the shema from step 1
Load the data using the unload from step 2 and the optional Step 3

Re: Drop corrupt table

former_SQLA_member1694892 — Fri, 02 Jul 2021 16:29:04 GMT

Thank-you so much for your input! This is a datamart db, and for the most part can be recre"ted via our nightly pipeline. For that reason, we are continually truncating the log file (-m option). However, there are a couple of transactional tables in the datamart and of course they are the ones corrupted. We believe there are 2, based on multiple queries. We can't even "select top n..." from either. Our thought was to drop them and recover data from a 2 day old backup.
Although I thought of the unload, which was unsuccessful, I did NOT know about the exclude table option so thank-you for that! It may be our best bet and thank-you again. A side question... Although the majority of the tables are fine, if we try to query via sybase central by clicking on a good table name and right clicking, it brings the whole db down. As soon as you click on the table. (bringing up an empty isql window and querying is successful on good tables). What happens within Sybase Central when you click on a table that brings that corrupt table into play? We just thought that was weird. Thank-you! Becky Snyder Bradley University

Re: Drop corrupt table

VolkerBarth — Fri, 02 Jul 2021 16:46:22 GMT

Hm, does DBISQL only (outside of SQL Central) allow to select from "good" tables?

In case of a "fragile" database, I would try to use "simpler" tools, even dbisqlc...

Re: Drop corrupt table

former_SQLA_member1694892 — Fri, 02 Jul 2021 17:17:55 GMT

Yes, DBISQL outside of SQL Central allows selects from good tables.

Re: Drop corrupt table

Breck_Carter — Sat, 03 Jul 2021 13:11:00 GMT

Would you be interested in a blog post that describes how to create a "mini-backup" process like the one used by Foxhound? It works well to frequently back up and restore small but important tables while excluding large tables that are not so important.

Re: Drop corrupt table

former_SQLA_member1694892 — Tue, 06 Jul 2021 07:52:34 GMT

Yes! Sounds like a nice solution for our datamarts. Some have their own more transactional tables.

Re: Drop corrupt table

VolkerBarth — Tue, 06 Jul 2021 09:36:55 GMT

Do you "mini-backup" to text files (like Foxhound) or also to a separate database via proxy tables (so both databases are only weakly connected)?