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

asked 02 Jul '21, 11:37

rsnyder's gravatar image

rsnyder
436121429
accept rate: 0%


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
permanent link

answered 02 Jul '21, 13:40

Chris%20Keating's gravatar image

Chris Keating
7.8k49128
accept rate: 32%

1

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

(02 Jul '21, 16:29) rsnyder
Replies hidden

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...

(02 Jul '21, 16:46) Volker Barth
2

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

(02 Jul '21, 17:17) rsnyder
3

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.

(03 Jul '21, 13:10) Breck Carter

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

(06 Jul '21, 07:52) rsnyder

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)?

(06 Jul '21, 09:36) Volker Barth
showing 2 of 6 show all flat view
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:

×34
×9
×7
×6

question asked: 02 Jul '21, 11:37

question was seen: 1,047 times

last updated: 06 Jul '21, 09:36