Database size, User count, Number of attachments - how much is too much?

[ctownj30]

We are considering how much longer we can continue using ACT - we've grown quite a bit and at this point have:

Active Users:  34
Database size: >5 GB
Attachments:  ~43,000

We are currently running on SQL Express but are about to upgrade to Standard (see my other question about that).

We are currently running ACT on a big, mean virtual machine and have no performance issues (other than lag/crashing very occasionally when certain types of searches are run).

I've seen references in the forums to others running more users, bigger databases, etc. - but how big is too big?  Assuming we're happy to throw hardware at the server, and run SQL Standard, how far have people pushed this?

What sort of problems might start occurring from sheer size, and when does that start?

Thanks - djm

[Richee]

The largest database we have is 60gb and that's for 12 users. 

59gb of the database is from attachments alone, I have spoken to the users multiple times and tried to train them on what not to attach but it seems to go in one ear and out the other.

As an example:

4 .PDF documents at 75MB each were attached to a single contact.  The pdf files are identical and they were attached by 4 different users. 

The help desk hate them and they hate the help desk all because they have no interest in understanding what is causing database issues.  

[ctownj30]

Thanks richee - so with the database that size, you're seeing issues? What sort of issues?

[Richee]

It's not so much the attachments that's causing the issue it's because the users don't want old data removed.  Due to the accumulation of old notes and cleared activities (from the 90's) they have slowness moving from contact to contact, navigating notes, looking at tasks and history.  The database is over loaded with old data.

I've offered to create an identical database minus the old data over a year old which will allow the current database to be used as an archive but they are not interested.

In my experience having masses of attachments doesn't really impact the performance but it'll give your server team a headache with the masses of storage space needed to keep everything backed up. 

[ctownj30]

Gotcha - so basically - if you're trying to scroll through contacts and each contact has 100 history items, each contact takes a while to load - makes sense.

We are in a similar situation but intend to just throw hardware at the problem - we are about to move to SQL Standard and a ridiculously fast server.

[Melville-Schellmann]

I FULLY agree with Richee. All his observations are 100% accurate.
One additional point which I would like to mention is this one: any given database will increase its size drastically if you add a field of the type "picture".

My example: 95 users 70,000 contacts, 63,000 companies,10,000 Opportunities, 2,500 Groups, 1,5 Contact million histories, about 500 custom fields & 80 GB attachments (= 120,000 files) was running OK (on a physical machine with 2008 SQL STD) with an ADF size of 4,8 GB until someone created a picture field to include company logos in the OPP layout. Right after the field creation (with NO DATA in any OPP!), the ADF size jumped to 9,5 GB, (which is typical behaviour btw for an SQL database, as it "reserves a LOT of space in advance).
The overall performance was bad from that moment on & the field subsequently removed.
Keeping this database shipshape before, needed only 1 area of "data" to be closely monitored: CLEARED ACTIVITIES. Any time this no. was approaching 20,000 in total, the database started to slow down especially for those users who were "the most active" (=having the most cleared activities).