Scheduled server script keep corrupting database

[IT Heroes]

Hi,

I'm running filemaker server advanced 10 on a Windows Server 2003. I've applied the latest filemaker server updates available.

I have a few imports from an Access Database that happen on an hourly basis. Probably about once a week I find the files that the scripts import to are corrupted and I need to run a recovery on them. The filemaker server just marks the database as closed and then refuses to open them. There are several scripts and several files. They don't all corrupt at the same time, it appears to be quite random. Other files are fine, it is only the files that are imported to.

I couldn't find any indication in the logs of anything going wrong, just that the script could not be run next time because the file is not available.

I just wanted to also mention I have a windows task to restart the filemaker service each night because we found it used to freeze up after around a week and would need the process being killed and restarted. The restart schedule seemed to have fixed the problem. It only started happening after the import script was installed, so I am assuming it is to do with that.

My next move was to move the import into another file, then have another script to copy over the data, but that would be a lot of work because I would need to duplicate the import logic in a script. Or perhaps I should try importing from access into an "import" filemaker file, then from that filemaker file into the target filemaker file. But this still wouldn't solve the problem, it would just mean it doesn't hold up the end users while I manually recover the file.

Any ideas?

Thanks,

Ryan

[Vaughan]

It sounds as though the hosted files are being accessed directly from the disk while open FM Server. The correlation with imports may be coincidence.

The need to frequently restart FMS invariably all points to a sub-optimal setup.

Ensure that the box that is running FM Server is configured so that NOTHING touches the files in the Data folder except FMS. Disable virus software, indexing software and backup software from accessing the Data folder.

Databases hosted in FMS are rock solid. The only times I've seen files that need constant recovery is when the setup is incorrect. In one recent case the server was fed dirty power. After installing a UPS the files have been 100% reliable for several months (they previously required recovery a couple of times a week).

[Wim Decorte]

I concur with Vaughan, FMS is usually rock solid and will not corrupt files by itself.

You mention that you recover the files from time to time: do you keep re-using those files? If so that's probably where the issue is. Recovery is meant to bring the files back so you can get the data out, it's not meant as "maintenance" so you can keep using the files.

As Vaughan mentions: double-check your deployment to make sure nothing at all can or does access the live FM files except for FMS itself.

How did you implement the Windows task schedule to restart FMS? If that is done the wrong way it may have resulted in the files being damaged.

[Steven H. Blackwell]

Wim says:

You mention that you recover the files from time to time: do you keep re-using those files? If so that's probably where the issue is. Recovery is meant to bring the files back so you can get the data out, it's not meant as "maintenance" so you can keep using the files.

Everyone please pay attention to this. This is very important information.

Steven

[IT Heroes]

Hi, Thanks for the replies. The recover tool isn't very helpful in saying that the database is ok to use is it!, nor the consistency check very convincing! . I'll create a new file and copy everything over... that is going to take a lot of work ... but nonetheless it shouldn't have corrupted in the first place... and it has happened on 3 separate files.

Filemaker server runs the backups on the databases, then the backups are copied daily by the backup system. It uses shadow copies so it shouldn't interfere with any database operation.

I'll check the anti-virus to see if it is scanning .fp7 files.

The server restart script calls net stop fmserver and then net start fmserver, I would imagine stopping and starting the service should be an ok way to restart it? I thought the issue with the server freezing may have been an issue in the Access odbc driver. I've read of similar issues with MS SQL Server when working with Access databases. So I thought the only way to fix that might be to restart the process periodically to stop any memory leaks caused by the driver.

Perhaps it is to do with the increased load on the server on those files due to the import and not the actual import operation itself causing the issue. Is it possible for a high disk load on the server to cause corruption? I would imagine only with a power outage or something like that while the database is being accessed. The server runs on a UPS and has a good power supply.

Is it possible for a high disk load to cause the issue?. Say for example, the filemaker database backup is running, the server backup is running, the anti-virus is doing a scan and suddenly a scheduled import runs on a filemaker database.

If the general consensus is that filemaker server is rock solid, I'm almost thinking I should just move it to a different physical machine.

Thanks,

Ryan

[Wim Decorte]

It uses shadow copies so it shouldn't interfere with any database operation.

Shadow copying, like Indexing has been know to corrupt files if it includes the live FM file folders.

The server restart script calls net stop fmserver and then net start fmserver, I would imagine stopping and starting the service should be an ok way to restart it?

No! The proper way is to disconnect all clients, close all files, stop db engine then stop the filemaker server service. See my VTC training tutorial for a VBscript that does all that.

The way you're doing it may fail because because you start at the end and that has to force all the other actions (disconnect clients, close files, stop engine).

You mention backup software: make sure you exclude any live files from the backup. That is also known to cause corruption. A better way is to push the FM backups files to a network volume and avoid running backup software on the FMS box at all

[IT Heroes]

I'm going to move it onto a new box. So then I can disable the anti-virus and shadow copies and lock the system right down. Am I better off with a server operating system, say windows 2008, or is windows xp or windows 7 fine?. I'm not using web publishing, so I don't need IIS. I don't see the benefit of a server os if it doesn't use any of the server functionality of it!. But if FM server runs better on a server os then I'll organise that.

Thanks for your guidance. I don't rememeber the installation guide say anything except that it isn't recommended to put it on a domain controller, and pretty much any server software says that!.

[Wim Decorte]

I don't see the benefit of a server os if it doesn't use any of the server functionality of it!.

??

FileMaker Server is ...well... a server product so it benefits from running on an OS that is tuned to server tasks.