DB Consistantly Corrupting (1 Viewer)

[Larnu]

HI all,

Although i have another topic at the moment, I'm starting another, as the issue seems to have changed as fixes have been attempted. As a result, I felt it may be better to make a fresh start in the topic thread, so that I can highlight exactly what's happening, and what we've done to try.

Here's a brief overview of what happened at the start of the issues:

• On Saturday morning (02/11), the backend database corrupted
• I was notified on Monday morning of the issue
• I compacted and repaired the back end
• After this, I found a large quantity of records in one table that were filled with #'s (about 100)
• In additional, there were about 20 more records containing random alphanumeric strings. Some of these has autonumber ID of over 10,000,000, even though the current ID is about 130,000.
• I deleted these records, and checked that the fe booted fine (which it did).
• Shortly after asking the team to go back to use, the be corrupted again.
• Another repair showed some more #s in the records, but only about 4.
• Again, the be corrupted, so i asked the team to hold fire.

Currently the system is still having issues, and is corrupting frequently. The time is takes seems to be sporadic (I had two team members test it at one stage, and it lasted 5 minutes, then at a later time 20, and it lasted 8 hours).


Currently the issues I'm facing are:

• The database corrupts after use in the live environment. The time to corrupt appears "random", but is sometimes instant, others takes hours.
• #'s appear in one of the tables, although no records are lost
• Primary keys are also being lost on the same table
• Duplicate data is appearing in the same table (the duplicates appear to be random, as one duplicated record was 6 months old, and hadn't been editted since then)
• Relationsip between the table, and others are breaking.
• Sometimes after a Compact and Repair, a table named "MSysCompactError" appears with data I (see below). Note, there is no field "Description" in tblRequest.

Example data:
ErrorCode ErrorDescription ErrorRecid ErrorTable
-1206 Could not find field 'Description'. ꬂ tblRequest
-1053 Could not find field 'Description'. tblRequest


I have so far attempted the following, and I'm still having issues, so I am assuming that they haven't worked:

• Compact and Repair
• Updating the db (fe and be), to Access 2002 from Access 200 format
• Converting the fe into a MDE file, instead of MDB.
• Providing a fe client to each agent, instead of using a central copy.
• Creating a temporary tblRequest table, copy the data across, and deleting the old one, before copying back again
• Creating a brand new back end, and then copy the data across using the import function (excluding MSys tables).
• Creating a brand new back end, with all the tables empty. Re-entering system required information, and leaving the rest blank. The new be corrupted as well (on the same table)

I've also been advised that copying and pasting may cause corrupting in text/memo fields. Unfortuatnely stopping the agents doing this would be almost impossible (as when we're using it, we have aout 200 people (not all at the sam time). Also, the system has been up and running for over a eyar, and this is the first time it's ever done this.

I haven't made any changes to the be and fe prior to this happening, so I'm sure that it wasn't me doing something prior to the weekend (plus I always make sure that if I am doing anything, it goes in on Wednesday, so I have time to revert/fix).

At the moment, I'm completely stumped. Every attempt I've made has failed so far and anything I'm getting through Google advises me to take steps i've already taken. :banghead:

At this stage, any advise people give to get us back up and running would be greatly appreciated. As i've been working on it for 5 days and feel I've gotten no where (and still no understanding to why it's happening), I'm pretty concerned.

Many thanks everyone of any help you can give, and I'm happy to answer any questions you may have to the best of my ability.

Also, sorry, i only just thought of saying this, another Access database we use seemed to suffer a similar issue. The applciation isn't anywhere near as complex, and I haven't done any of the steps above apart from compact and repair, but it also went down at approximately the same time on saturday. Does anyone know if there's anything that could cause two independant Access db's to corrupt repeatedly,a s this db does also fail after I compact and repair. Again this has the same errors in the MSys table, even though a field "Description" doesn't exist in the table:

ErrorCode ErrorDescription ErrorRecid ErrorTable
-1206 Could not find field 'Description'. ᬖ tblContact
-1206 Could not find field 'Description'. ᴉ tblContact
-1206 Could not find field 'Description'. ḃ tblContact
-1206 Could not find field 'Description'. Ḅ tblContact
-1206 Could not find field 'Description'. ḅ tblContact
-1053 Could not find field 'Description'. tblContact

Kind regards,

Tom.

 

[spikepl]

James Dudden provided you with two links in this post : http://www.access-programmers.co.uk/forums/showpost.php?p=1306624&postcount=15 but you have not reacted to that. Read the links and go through the suggested causes of corruption.

 

[Larnu]

Hi Spike, I am reviewing those, but much of that suggests the above, or otherwise isn't applicable. As stated in my post, I've moved the topic beeause i wanted to have a new thread which showed a better picture of what I'm having (and you can't change the title of the thread either, and the #'s isn't the only problem anymore so is misleading.).

The link on granite.ab.ca I felt might be useful was "Multi-user Database File Corruption", is a dead link, so coudn't be used, and on allenbrowne.com, the only part which has details relevant to my issue is the 'Recovering from corruption' section, which I have performed many times over the week.

Everything Access advises that for multi user enviroments to turn "opportunistic locking" off on Windows NT servers, however, increases risk of corruption. Either way, I'm not in a position where I'd be able to have to this changed, even if i needed to.

 

[spikepl]

If you want help, then say what you have tried with what results, so that the readers get more clues and do not need to wander off into blind alleys. The links give a number of suggestions, so saying "some are inapplicable" is not helpful. WHICH? A number of them relate to hardware issues - and here we are, not knowing whether you have tried them, ignored them for some reason or just missed them ...

 

[jdraw]

Here's another granite comment on corruption
http://www.granite.ab.ca/access/corruption/corruptobjects.htm

Good luck.

 

[Larnu]

Topics from Allenbrowne:

Recovering from corruption - Repeatedly completed this step, as advised in my post.

Symptom: Cannot open a form or report - not applicable

Symptom: Number of records varies, depending how the data is sorted - not applicable

Symptom: Some table rows show #Deleted - The describion the site makes is different. I see rows of #'s, with the autonumber also being a hash. I have, also performed steps 5-8 previously, as described in my post

Symptom: Memo field contains strange characters. - not applicable

Symptom: "An error occurred while loading Form_FormName" - not Applicable

Symptom: "Error Accessing File. Network Connect May Have Been Lost" - Not applicable

Symptom: "AOIndex is not an index in this table" - Not applicable

Symptom: "<Database Name> Isn't an Index in This Table" - Not applicable

Symptom: Key field is no longer primary key, and relationships are gone - Applicable, again, i have performed this each time i have repaired.

SaveAsText / LoadFromText - The form isn't corrupting, as the back end has no forms. The front end has been stable.

With regards to hardware, we're running on terminals, so everyone is using the same resources (apart from possibly being logging into a different main server). The actual database is hosted on a network drive. A possible route from those toics, which I'm trying to pursue with our Systems department since yesterday, is regarding the traffic over the network, and if we can have the be moved to a server that may be less conjested.

 

[jdraw]

Multiuser FE/BE would have BE on a shared facility and a copy of the FE on each user's PC.
If you have been using a shared FE, chances are that is the heart of the issue.
Memo fields have often been identified as problematic (corruption prone).

Good luck.

 

[Pat Hartman]

Is this happening in one table or multiple tables?

When you distributed the FE, did you just copy the FE they were all originally sharing? It is quite possible that the corruption is in the FE and some situation in the FE causes a table to corrupt.

I would rebuild the FE and then distribute a fresh FE to all the users.

 

[Larnu]

When you say rebuild, do you mean recreate every form, or just recreate the MDE? I have about 45 forms, so this could be quite a lengthy process.

Thanks.

 

[syswizard]

Have you tried to DE-COMPILE ?

 

[Larnu]

Gave De-Compiling a go at the weekend, along side recreating all the tables and porting the data across afterwards with SELECT statement. Lasted about 24 hours before failure.Trying to get our Systems team to set up a SQL server for me, so I can try that route.

Found out that across the whole business (which isn't small), we have a total of 2 data centres, so the volume of requests going through them is insane. Guessing that's likely why it's now failing, especially with the influx of staff for Christmas.

 

[Pat Hartman]

By rebuild I meant - create a new empty database and import all the objects from the original version. You will also need to set any necessary references and startup options.

You need to track down the form that is causing the corruption. Have any been changed recently? Can the users help pinpoint when the corruption happens?

 

[Galaxiom]

Access backends will corrupt if there are network dropouts or excessive latency. Are any users on wifi or connecting via a WAN?

Have your IT guys checked for the possibility of there being network hardware problems?

Overloaded segment of the network. Networks sometimes grow by tacking on bits without properly analysing whether it can support the load.

A server backend would be a better solution but you wuld need to adjust your front end to take advantage of its full capabilities.

 

[ButtonMoon]

Access backends will corrupt if there are network dropouts or excessive latency. Are any users on wifi or connecting via a WAN?

True, and that alone is a good enough reason not to even consider using Jet/ACE for a system with 200 users. Larnu, what is your backup and recovery strategy? For the sake of argument, suppose you backed up such a database twice a day. In a crash you would be faced with losing up to 100 person-days of work. Is that acceptable to your business? Or would they be willing to accept the downtime necessary to backup a consistent version more frequently than that? By using a server DBMS you can eliminate the possibility of "corruption" due to networking issues and you can ensure data is properly recoverable (to a recovery point of a few minutes absolute maximum I should think) from log-based backups without any downtime to take a backup.

I'd have no hesitation in recommending an upgrade to a DBMS like Microsoft SQL Server. I think it will work out much cheaper and less trouble than persevering with what you have now.

 

[Larnu]

Hi Button,

This is actually what I've been pursueing with our Systems Department since Monday. I discovered in the morning, after talking to someone else in the business, that they've been suffering with the same problem that I have. As the client isn't mine, or used by the same team, it does seem to be a hard ware/network issue.

We have plenty of SQL servers fortunately, however, waiting for Systems to set up an account me is taking time. Hopefully once we're online then I'll start to see some progress.

 

[Pat Hartman]

I've worked for clients where it took longer for their "no help at all" desk to set up an account for me than it did to do the actual conversion. At this particular client, new-hires wait for 2-3 weeks before they have an account with email. They flatly refuse to start the process when the applicant accepts the job and somehow management doesn't think this is a problem. Of course, they were the ones who sold the idea of outsourcing the desktop support group. Mouse broken? Go to Staples and spend your own money or wait 2 weeks for a new one.

 

[Cronk]

I would have written the same as Button. I've had corruption problems at 2 sites in the past. One proved to be a faulty network card, the other a flaky network.

To save data loss in the meantime, I created tables in another db and as each record in the BE was appended, or opened for viewing/editing, copied the record to the other table. Slowed things down but meant critical data could be restored.

 

[ButtonMoon]

We have plenty of SQL servers fortunately, however, waiting for Systems to set up an account me is taking time. Hopefully once we're online then I'll start to see some progress.

Maybe you don't have to wait. You could start development on your own workstation (using SQL Developer Edition or Express). In some development teams the developers work on individual workstations and only deploy to the shared development server when they are ready to check in. That's a method that's worked for me in the past.

 

[Larnu]

Maybe you don't have to wait. You could start development on your own workstation (using SQL Developer Edition or Express). In some development teams the developers work on individual workstations and only deploy to the shared development server when they are ready to check in. That's a method that's worked for me in the past.

I wish I could, however, unfortuatnely I use a terminal, so not something I can do.

Over a week later, and I'm still waiting for them. At least the boss is also throwing his toys out of the cot now, and they're aimed right at System Support's head.

 

[mafhobb]

Cronk,

How did you do that? How did you create another db and copied the data into it automatically?

mafhobb