Cary,
Part of the issue here is that, in my opinion, and completely understandably, those in tech support will be very good with AC but not necessarily with SQL. SQL is a rather complex database. Given I attended their lecture this morning I should have asked them and Jon if any of them are SQL experts and/or does he plan on hiring one.
Looking at your situation, it is a common one, and I am surprised it does not come up on the boards more often.
In your situation, it shouldn't matter whether you move to v5 or not, and I wouldn't let this change that. Basically, SQL is just doing what it is supposed to do. SQL is designed to use memory dynamically, taking what it needs and giving back what it doesn't. Unfortunately, it tends to forget about the latter. It is important to understand that SQL itself is a 32-bit program requiring memory. But, it also acquires memory due to two reasons. One, every time it reads a page from disk, it allocates that page to cache so it doesn't need to obtain it from disk next time. Second, it piles up memory in its procedure cash. Every time there is a query, SQL comes with execution plans as to how best handle that query. It then chooses the most efficient and dumps the others and attaches it to that particular query. Computational queries are very "computer intensive." When SQL reaches its maximum amount of memory allowed it gives back some of this memory by deleting the pages and plans that have not been used lately. Unfortunately, from my understanding the maximum memory allowed is a mere 2.1 million GB.
Fortunately, it is rather easy to change the minimum and maximum settings. I could easily do this for you or recommend how, but given there are possible implications on the maximum, I should get more info from you, AND, I would want to ask my SQL expert.
Ironically, it sounds like you are a victim of NOT using SQL Express as it is limited to 1GB of RAM. AC doesn't need a lot of RAM for it to run well, and there are probably many, many stored pages and procedures that you don't need.
What SBS are you using. Are you using 32-bit or 64-bit. How many users and clients. Are you using SQL Server 2005 Workstation or Work groups.
It is not a virus.
If only it were that easy.
