John, you read my mind.
I thought I would weigh in. As I am nowhere near a SQL guru other than being able to spell it, I did go to my favorite site for some help from the SQL experts there. Rather than pick out certain ideas or make them appear they are from me, I have permission to copy and paste it directly: (this is a post from a user there)
The problem isn't necessarily 30 users on SQL Express per se, but what those 30 sessions are doing, how active they are, etc that can cause memory issues, etc. If the sessions are lightweight you could support a hundred or more, or if the sessions are very very active, lots of long running transactions, etc., you could blow up at five (not likely but possible). Since the diagnostic routine is having trouble shutting down SQL Server I am inclined to believe that the activity between the clients and the db are on the heavy side. On the AC website they indicate that the target is 1-5 computers. Very often in that scenario db coding is not highly optimized. All this to say that while there are no guarantees, there is a decent chance that SQL Server Workgroup edition or higher would solve the problem. There are numerous avenues to get 90 or 180 trial days of SQL Server so that it would be possible to road test the solution without paying up front. If the upgrade proved out, then you could pay for the licenses, etc.
Also from reading the AC forum post on this issue, it seems that the issues occur as the load approaches 28-30. This seems to argue as well moving up from express. Of course the point of the upgrade is going from 1G to 3G of RAM, so just upgrading the edition without making sure that the additional RAM is accessible to SQL Server won't get you anywhere.
Finally, there are other potential issues such as network congestion, that could be contributing, causing the problem, but in this situation getting an eval copy of SQL Server would probably be the simplest diagnostic step to take.
From Bert: I also wonder about the number of AC clients being used and would love to know how many other practices have that many users. Did support comment on that? Certainly not trying to scare you aware from AC. If you have the memory on the server, it will definitely take it. There really are no "memory leaks" which are famously attributed to SQL, it's just that it will take what it can get. So, with 4GB, really = ~ 3.2GBs, you will definitely get more.
I agree that part or all of this may be your overall network. I would tend to want to start from the router to the switch to the server to the clients and make sure everything is 1GB or whatever you are set up for. You could also consider upgrading the server to 2008 or even SBS 2008. SBS would give you the ability to have 75 users and would have the advantage of having SQL Server 2005 already installed. And, it is 64 bit, which will allow you to add tons of memory depending on the motherboard. My guess is you are running 32-bit on Windows Server 2003 and could go to 64 bit if your hardware supports it, but if you do upgrade, you may as well go to 2008 and, if money is not an issue, consider SQL 2008.
You could also simplify things and try taking certain things off the network such as just not using or disconnecting the terminal server ones. Just curious, why are you not using a domain. My guess is it is because with that many computers, some don't have the correct OS?
...our primary data file increased from 600MB to 800MB after upgrading from V4 to V5. We might be forced to switch to other EMR when the data size is approaching 1GB. We really need some help!!
That wouldn't be AC's issue, it would be SQL Express. (although it is 4GBs of data and 1GB of RAM as mentioned above) You do not HAVE to run AC with Express, in fact I don't. I also have 20GBs of RAM which is an entirely different story.
