Tuesday, November 18, 2008

Max Locks Per File

I have a replicated MS-Access database that is used to store archive data from the production system. This archive is Access 2002 format and is approx 140 MB in size.

The design master was recovered from a replica & saved to a local C: drive, and then a new replica was created & published to a network share. So far, so good.

However, when I attempted to sync the two files, I received an error that the MaxLocksPerFile setting in the registry was exceeded and the sync could not be done. The "Help" button said that the default was 9500 (decimal), and suggested that I use SetOption to change it, but SetOption() has no such option.

I found it curious that a brand-new replica would require the complete sync process even though nothing was changed in either design or data, but I digress...

In any event, I did find the key in the registry by that name, it is located at:

HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile


I changed the setting to 12,000 and the progress meter showed two bars and then froze with the error message. With 15,000 it reached five bars; and finally 25,000 which worked. The progress meter displayed the typical appearance & the sync was successful after a few seconds.

I then went back to experiment with a lower setting of 20,000 to try to find the go/no-go level but the sync was nearly instantaneous, so I suppose that the act of moving the replica forced Access to run the sync process on the entire contents of the file.

No comments: