What causes a database file size to bloat (file size increase)?

[cheekybuddha]

Databases that are shared should run on SQL Server.

That's a bit of a blanket statement with no qualification.

Additionally it costs a lot of money!

If you are on a LAN then an Access backend can likely do the job as well.

Otherwise, what about MySQL, PostgreSQL, SQL Server Express, etc ... as options?

 

[cheekybuddha]

There is a free version

Oops! Missed that!

 

[Thales750]

That's a bit of a blanket statement with no qualification.

Additionally it costs a lot of money!

If you are on a LAN then an Access backend can likely do the job as well.

Otherwise, what about MySQL, PostgreSQL, SQL Server Express, etc ... as options?

Why is it more expensive?
In no way can a backend do as good of a job. No security, no professional grade User management, and when you get above 20 users you are guaranteed to have data corruption.

I don't disagree about any other of the DBs you mentioned. Just not a file bases system, like Access ACE.

 

[CJ_London]

when you get above 20 users you are guaranteed to have data corruption.

In my experience, that is not the case. I have developed several apps over the years with more than 60 users with both jet and ace back ends, never had a problem with corruption.

Sql server (or express) was not an option because of IT politics - they wanted to own the whole thing, but did not have the resource or budget to develop it.

 

[NauticalGent]

I *thought* the decompile was for code?
Do you have code in the BE?

No, I just added that on since in was talking about my maintenance routine. I do a C&R on both and decompile/recompile the FE at the EoY

 

[Thales750]

In my experience, that is not the case. I have developed several apps over the years with more than 60 users with both jet and ace back ends, never had a problem with corruption.

Sql server (or express) was not an option because of IT politics - they wanted to own the whole thing, but did not have the resource or budget to develop it.

I've been on industrial installation where network issues caused problems. How does using SQL Server Express fall into the category of ownership? Does Microsoft retain ownership?

I went for many years using Access backends. but after biting the bullet and moving to SQL it just made perfect sense. It does have a learning curve, but it also makes the developer more versatile.
I suppose I should have said that the odds of data storage issues increases with lots of users and large numbers of records.

 

[The_Doc_Man]

How does using SQL Server Express fall into the category of ownership?

I ran into this sort of thing with the Navy. It isn't that Microsoft does or doesn't own the SQL Server software. It is that if it is a server, there is always some centralized IT group that says "that server is automatically our jurisdiction and responsibility." Then they take it over and it never works right again because they don't understand the problem being solved by the DB that was being built.

 

[Galaxiom]

I guess from reading below that i am creating temp files everytime I query, and they never go away until compacted. Is there another way to do these queries without expanding my file size.

I have long argued that putting temp tables in the front end is the equivalent of writing a shopping list in margins of an encyclopedia.

Temp tables belong in a separate database which I call a "Side End". There are multiple ways to implement a side end. The easiest is to keep a pristine file with the required empty tables and copy it to the user's workspace (eg Appdata) when required, then simply delete it when no longer required.

 

[Galaxiom]

there is always some centralized IT group that says "that server is automatically our jurisdiction and responsibility."

I am fortunate to have always been part of that IT team in my workplace with full access to the network configuration and database administration. Eventually I settled into the role of the designated database administrator as we took on more IT staff.

The size of the business I'm in is now very close to the sweet spot for being a database developer and administrator. Small enough to manage and implement virtually it all myself which is great for extending my skills without jumping through hoops to get formal permission to do stuff. But with specialist staff to manage and support the third party applications and let me maintain focus on the database integrity and availability.

The three database application staff and I have the highest respect for each other. It makes a big difference. I've been in a situation where I was not respected by a developer colleague. I realised his disrespect was out of fear that I would recognise he was completely incompetent, something I did know, and fortunately management eventually worked out too without me having to drop him in it.

 

[The_Doc_Man]

Unfortunately, in 28 1/2 years with the U.S. Navy, I have seen more adversarial situations than non-adversarial.

 

[Galaxiom]

Unfortunately, in 28 1/2 years with the U.S. Navy, I have seen more adversarial situations than non-adversarial.

I think that is a fairly common situation. Many database administrators subscribe to hating Access. Some because they have seen troubled caused by Access dabblers who haven't got a clue what they are doing but many just to be part of that culture,

 

[Thales750]

I ran into this sort of thing with the Navy. It isn't that Microsoft does or doesn't own the SQL Server software. It is that if it is a server, there is always some centralized IT group that says "that server is automatically our jurisdiction and responsibility." Then they take it over and it never works right again because they don't understand the problem being solved by the DB that was being built.

When I was at Home Land (TSA Headquarters) we had the same issue. I had forgotten that. We were not allowed to have an SQL Server or even part of theirs. We weren't allowed to share one of our computers as an adhoc server. We were a group of undesirables, they put our backend on high traffic servers until I finally convinced them we need better bandwidth. That was my one and only true political battle, and I won. lol.
Thanks for reminding me of that Doc.

 

[The_Doc_Man]

I think that is a fairly common situation. Many database administrators subscribe to hating Access. Some because they have seen troubled caused by Access dabblers who haven't got a clue what they are doing but many just to be part of that culture,

Many more of them just don't understand Access and its inherent dynamics that are a bit different than "the big boys." Because Access uses SMB protocol, which is the same exact protocol used for Windows File and Printer sharing, they can't fire-wall without extra work. Otherwise, nobody could use a network printer or network-attached storage. They feel like they are losing control. I (regrettably) had to use office politics to get my government supervisor to tell IT to get out of the way of a necessary internal application we used for tracking the status of our (over 1500) servers. They HAD to relent when we pointed out that we could give them detailed patch status and patch history AND detailed reasons for patch delays. After a while, they caught on that it wasn't such a bad thing. But it took a bit of juggling to get them to back down.

 

[Thales750]

Because you need a DBA to manage it. Application developers know enough about SQL Server to be dangerous. They don't know how to optimize it or ensure that backups are timely and usable for starters.

As others have mentioned, temp tables are best stored in a separate disposable database. That way, you only have the bloat you created since the last time you started an import process.

Gave up on that idea years ago. Now all "temp" table are permanent SQL tables that are kept organized by using the Computer Name, or the user Login. Depending on how the system is setup. I prefer Login because they are portable.