Community
Showing results for 
Search instead for 
Do you mean 
Reply

Mass changing contact fields started freezing at 102 contacts

Copper Contributor
Posts: 66
Country: USA

Mass changing contact fields started freezing at 102 contacts

We have a database with 300k leads in it. Everything has been running smooth on our dedicated windows 2008 server with 24G of Ram and 16 processors. However, just this last week, trying to update multiple records at the same time (anywhere from 500 to 10000) the process freezes at 102 records for about 5 minutes,MAXING OUT THE SERVER the entire time, then continues on at a crawl. 

 

Every week, I assign out contacts to reps by the thousands, averaging 10000 a week (a few thousand nightly) and also importing and duplicate checking another 20-50k leads in the same time frame. This process has gone from a couple hours total for everything to a couple hours a night because of this issue. 

 

I have run all the ACTDiag utilities, check and repair, and even deleted and recreated different admin accounts to see if there was something wrong with the one(s) I was trying to use. All to no avail, the issue persists. 

 

The interesting thing is, if I kill the task (ACTSage.exe) on my machine when it's frozen, the server instantly unfreezes and goes back to normal operation, it's like my machine is chiking the whole server. The same thing happens on other machines and even on the server when logging in with differrent admin accounts so I think I have rulled out the user accounts and the computer...what else could it be? The database? I ran all the repairs and everything else works fine, it's just changing more than 102 leads at a time that couses the issues.

 

Sending our database to ACT support is not an option, it's not something the company will ever agree to since this is our sole source of contact info for the whole company, we can't be without it for one minute. Please help!

Bronze Elite Contributor
Posts: 2,546
Country: New_Zealand

Re: Mass changing contact fields started freezing at 102 contacts

Questions: -

Which version of ACT!?

How many active users?

What version of SQL, i.e. Express, Standard or Enterprise?

Exactly what do you mean by 'update' - What is the process?

What do you do to assign out contacts to reps?

Does the database synchronise?

What is the source of the data you import e.g. Excel, tab delimited, Outlook?

Was there an abrupt change from working OK to now working poorly, or has it been a gradual decline?

 

  

Graeme Leo
Xact Software - consultants and developers
Follow us on Twitter and check out our Blog


Copper Contributor
Posts: 66
Country: USA

Re: Mass changing contact fields started freezing at 102 contacts

[ Edited ]

Which version of ACT!?

ACT! by Sage Premium 2010 Version 12.1.181.0, Hot Fix 5


How many active users?

85 (but I do all the assignments after hours so this doesn't come into play during that time)


What version of SQL, i.e. Express, Standard or Enterprise?

Standard


Exactly what do you mean by 'update' - What is the process?

Edit -> Replace Field


What do you do to assign out contacts to reps?

Replace the record manager and another field that has securiity on it (so that nobody else can change it)


Does the database synchronise?

No - had to many isues with that, we have remote users just VPN to Virtual Desktops now


What is the source of the data you import e.g. Excel, tab delimited, Outlook?

Importing is done from Tab Delimited Leads into a main repository database and then exported into the working database after duplicate checking. I have the same problem with the repository database also. 


Was there an abrupt change from working OK to now working poorly, or has it been a gradual decline?

It was abrupt. One day, it worked great, the next it didn't.

 

** UPDATE: I was troubleshooting last night (early this morning) and I found that if I change any field that doesn't make a change to the history, I can change a couple thousand contacts in a few seconds, but as soon as I change a field that makes a history update, it freezes again. I do "Remove Old Data" every week. I remove histories older than 90 days and cleared activities older than 60 days. This is put in place based on management policies and I don't think I can change it without a valid reason other than, "ACT isn't functioning the way it's supposed to so we have to disable histories." 

New Member
Posts: 4
Country: USA

Re: Mass changing contact fields started freezing at 102 contacts

Well, the community would not allow my login to work anymore and support isn't helping, so I had to create a new account. I started this thread as CamWells. 

 

So, I have not been able to figure out a resolution for this. Some fields I an disable history after everyone is gone for the day, but, some I cannot (like the Record Manager field) so it's still a huge issue in our 300K+ database. I even went so far as to delete all histories up to the last 30 days instead of 90...still no go. I also noticed that if I delete the account I'm using and create a new one, I can usually get a few thousand changes done before it starts slowing down again; not every time, sometimes creating a new account has no affect. 

 

Anyome have any ideas on how to resolve this? 

Platinum Super Contributor
Posts: 5,275
Country: USA

Re: Mass changing contact fields started freezing at 102 contacts

The 'Remove Old Data' process will prompt for a re-index. Have you also tried running the Tools > Database Maintenance > Check and Repair option? Note: It is good practice to make a backup before running maintenance or mass updates (File > Backup > Database).
Greg Martin
Sage
New Member
Posts: 4
Country: USA

Re: Mass changing contact fields started freezing at 102 contacts

Yeah I have done the check and repair through the interface as well as through ACTDIAG - repeatedly. It's such an odd issue, we didn't have this issue at all until recently, so I thought that maybe it was the size of the table or something, but then I removed everything in history except the last 30 days (which ended up being over a million records) but that didn't resolve the problem. 

 

I think maybe the table index or view is corrupt or something, there doesn't seem to be any other issue that would cause such a huge problem on such a massively equipped server. Either that or the query that writes to the history is somehow messed up. The machine running th update freezes the whole server, so it sounds like a slow query causing the bog, but how do I test that theory, and what do I do to resolve it? 

Platinum Super Contributor
Posts: 5,275
Country: USA

Re: Mass changing contact fields started freezing at 102 contacts

[ Edited ]

Try these steps to see if they possibly shed some light on the cause and a possible fix:

 

- try mass updating the contacts in the demo database.  Does the same behavior exist?

 

- create a backup of the database (File > Backup), then restore it using the Restore As option to create a new database.  Test the update function within the new database.  The thinking being: a rewriting of the data and tables could help.

 

- from the original database > create an Empty Copy (File > Save Copy As > Empty Copy).  Open the new database, then go back to the original database.  Create a Lookup of several hundred contacts > Use the export function (File > Export > using Current Lookup option) to populate the Empty Copy database > when the export is complete > test the update function within the Empty Copy version of the database.  Unfortunately this option won't be a 'full' test because it doesn't contain all of the contacts that the original database does.  You can export all of the contacts, but with the number of contacts you have it could take a good while.

Greg Martin
Sage
New Member
Posts: 4
Country: USA

Re: Mass changing contact fields started freezing at 102 contacts

Thank you. The demo database does not exist on the server, so I can't check that one. However, I did create a new database and put all of our custom fields into it then imported about 500 contacts and everything in that database works fine.

I tried to create an empty file and import contacts into it...it got about 2500 contacts into the import, and then crashed. So I can't do that - I would have to do it in 2500 chunks which would take about 40 hours.

I have not tried a "Restore As" so that will be my next experiment.

If I can't get it working with that option, is there a tool that will guarantee all data (notes, companies, opportunities, etc) get transferred to the blank copy of the database that is working with an export or import since I can't rely on the built-in function?
Platinum Super Contributor
Posts: 5,275
Country: USA

Re: Mass changing contact fields started freezing at 102 contacts

There are a few import options available, they can be reviewed at the Add-Ons web site: ACT! Add-ons.

 

I've not used any of these products and cannot recommend any one over another.  As to guaranteeing the data import/export is successful, keep in mind that corrupted data in the original database may not be able to transfer.

 

Greg Martin
Sage