Single table seems corrupt - Causes FM Crash

[Visionjcv]

Hi Everyone,

It's been a while since I last posted on here, but I am up against a wall with this problem. I have been working at a new company which has possibly the most intricate and complex FM system I have seen to date.

Anyway, I took over here supervising the Filemaker system a couple of months ago and from the very beginning there was a very odd problem where periodically one table in one file would apparently become corrupt. Let me define corrupt in this context: Basically, one table would start causing problems in so far as if you edit (or create a new record) and then commit the changes, it would show the deadly coffee cup and stay that way indefinitely until you force quit the application.

Once this happened it would happen to users across the network consistently i.e. every single time any user tried to change anything it would crash. There are no script triggers or scripts executed upon commit or change etc. I have tried this in several layouts from external files and from the data file itself which contains the table and the problem is consistent throughout.

How we solve it:

- So far, all I do is literally reboot the server itself (Filemaker Server alone doesn't fix this). When it comes back up, the files all pass the verification process and open normally and then voila, no more problem.

However, this seems to be happening more and more lately. Today alone it's happened twice, and since the files are several GB in size, and there are over 100,000 connections to the database per day (through clients and XML Web Publishing) this causes a massive inconvenience and brings the entire company to a standstill.

There is no backup that goes back far enough to be sure this problem wouldn't happen. I've been tempted to perform a recover and run that file as the main data file, but have been strongly advised not to.

I have also checked and disabled any server scripts that could cause this problem i.e. a script running that makes records unavailable etc. but that isn't it either. I'm starting to seriously worry about this problem...

Any and all advice/suggestions etc would be very welcome.

EDIT: One other thing I noticed and forgot to mention is that when I try to run a database report it crashes (no matter how long you let it run) and you end up with a bunch of empty files that don't contain any information. Could it be that the database is corrupt as a whole? If so, how would I go about fixing this? Keep in mind this is a database with hundreds of layouts, dozens of tables, and possibly thousands of relationships.

Cheers,

Jason V.

[Vaughan]

Could it be that the database is corrupt as a whole?

It sounds like it is corruption. Continuing to use the file is only making it worse. It's a ticking time-bomb. Did you attempt to fix the corruption when it first appeared?

Get a copy of the database and delete the table that you suspect is the problem (yes, I realise that this will break functionality). Try running the DDR on it and see if it now completes. This may indicate where the problem is.

Fixing the problem may require a substantial re-build of the whole database. Copy/pasting elements from the old to the new file will substantially decrease the development time but may re-introduce some corruption, so the risk will ned to be weighed against the benefits.

You could start by saving a compressed copy and seeing whether that works; if not then go to a recovery.

[Visionjcv]

Thanks for your prompt reply.

I've only been working here for a couple of months and it was already an issue when I came (together with another million problems) and since a restart the first time seemed to solve it for over a month, the problem was just 'ignored'.

I am saving a compacted copy at the moment and suspect it will take a little while. I'll be sure to let you know the outcome of that. Regarding the recovery route, how 'bad' could it be to try and recover the file and then run that file? What are the potential pitfalls even if it 'seems' to work ok? I've always recovered a file simply to extract the data from it, but that was mainly in systems I had been a part of building and therefore backups were not an issue. Here however there is no backup I can use that goes back far enough to ensure this corruption doesn't exist in the file.

Thanks again for your help.

[Visionjcv]

Ok, I have just taken the compacted file online to replace the old one. Whilst it is 'working' the problem is clearly still there. Any commits on this table take about 5 seconds to go through whilst any other table is instant - It is just long enough to get a glimpse of the coffee cup.

I'm trying to run a DDR on this new file and it's failing again. So the next step is to delete the table in a copy and see what happens...

[Vaughan]

A recovered database is like a house that has been broken into... no telling what could be missing.