What happens if Access 2003 DOES reach its max file size?

[K1BBQ]

We make an alerting system that has an admin station, a Dell workstation running XP, that monitors system activity and stores stuff in an Access 2003 database. Something on the system ran wild a week ago and it spewed out a continuous stream of error messages and we found the database at a size of 2,097,124KB this morning in Windows Explorer. I found in Access Help an entry that says the max size is 2 Gigabytes, less the size of the system tables.

I figured that 2,097,124 was at the limit and it would not let the admin application do any more inserts. But there was live data in the database that was stored there just an hour before the overload was discovered. Either I found it just at the moment it reached the max, or maybe it allows inserts and deletes other data to make room for the new insert? How would Access handle a max condition?

 

[JoeAtWork]

In my experience you can't make any more inserts once the limit is reached. I don't know about doing edits.

In terms of shrinking the database, if a Compact & Repair doesn't work, you could try creating a new database and importing all the tables.

 

[psemianonymous]

It will definitely throw an error, so you should know exactly when the program fails to perform any action. I can't imagine what system you're using that actually manages to fill up the 2GB of space. Let me rephrase: I don't know how a database suddenly grows to the full 2GB size--if you are storing that much, consider moving away from Access or moving old data to an 'archive' database to remove some of the bulk.

The point I'm trying to make is that if your data approaches the 2GB limit, you shouldn't be doing what you're doing in Access: either change what you're doing or change your database backend.

 

[K1BBQ]

One of the system components, a touch-screen "Master Station" computer running XP, used for starting alerts and displaying icons for all the locations that beep in an alert, accidentally got a second instance of its software started. The customer somehow managed to minimize the application and since the Taskbar is auto-hide, got confused - where did the app go? - and decided to click the button to start it again. Well, that resulted in a mess with the main Host computer's communication with the Master Station and started a loop in the Host that generated an endless stream of message packets to the admin station, about 10 per second for a week. Those get inserted into the database, so it just filled up. I found in my logs (I've had more time to look into it since I posted) where it first started throwing exceptions, last Monday morning. From that time on, that insert query failed every time. It's written in VB.Net with System.OleDb, and the OleDbException says "Invalid argument."

But even after that point, which I am betting was the "full" point, there were inserts that did succeed. We record conversations during alerts, as .wav files that are FTP'ed to the Admin and inserted as blobs in a database table. Those were still successfully being inserted after the full point, I was able to get them out and listen to them. So I'm curious why one insert failed and another worked.

We use Access because it's rather cheap and easy to use. There is nothing particularly complicated about the database needs for the system. We store system configuration data and system activity that comes in as messages from the Host, so we can generate Crystal Reports on the system. Normally it adds maybe 10 to 20 mb per month to the file, and we have a utility built into the admin app for archiving data and purging older stuff, so it's rarely gone over 100mb.

We solved it by just swapping it out for an empty version of the database and starting it up again. The data is useful but not crucial, so we basically just started over clean. Fine with the customer.

 

[K1BBQ]

And we have it on the to-do list to make it so a second instance of the Master Station app cant be started

 

[K1BBQ]

Interesting response on another forum:

***************************************************
Typically the next time the system opens and attempts to run any action query you get an error message that says "Invalid Argument".

When Access runs any action query it initially attempts to replicate the effected object to allow you the opportunity to undo. During this record copying process Access also retrieves the overall size of the dB and copies the dB just incase during the action query the dB crashes. This is how Access restores a YourdB_Backup.mdb When 2gig is returned as the current file size this is an invalid value for the FileSize variable and the invalid argument is returned, halting further attempts at running action queries until the dB is brought down in size.
***************************************************

 

[Ed2020]

Whilst I agree with the previous posts about considering a different database engine (whilst possibly retaining your MS Access frontend?) why not try splitting the tables across multiple backends? This may alleviate your problem for a while at least buying you a little more time to consider alternatives....

Ed Metcalfe.

Please do not feed the trolls.....