Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Act 2007 Hanging

Status
Not open for further replies.

techytopia

Technical User
May 29, 2009
10
GB
Hi,

We have ACT by Sage 2007 (9.0) Version 9.0.1.162, Hotfix 1, English (United Kingdom) and also Sage Line 50 Accounts version 12.01.0034. We have recently had an issue where the users’ search windows hang or pause for around 10 to 15 seconds or so. The Act windows do not completely clear, as if the PCs CPU is running at 100%, which it isn’t, and the next window with the customers information on it does not draw or fill with details clearly until this delay has passed. Once at this point, there is much less slowness using Act and Sage together to create orders or invoices.

We run the Act SQL server on an IBM Eserver with a 3Ghz Xeon CPU running Windows 2000 Server SP4 with about 2GB of RAM, and it has proved perfectly adequate as there are only 5 users and about 13,000 records in the database. It links to Sage Line 50, based on another shared drive on the same server, with the ‘Accounting Program’ tab in the Act record, from which the users create new records and orders. This system has been running perfectly quickly, with almost instantaneous responses for about 2 years.

This happens on every search but is worst on a post code search, which is unfortunately the users’ initial process. The users first response to an incoming telephone call is to look up the customer on the database by post code, and this has started to impact the usefulness of Act in that the users have to tell the customer to wait until the system 'catches up', and brings up the customers details. This can take from a few seconds to more than 20 and has become a major issue.

We recently had a problem where Act was crashing a few times a day on every PC, after some Windows Updates were installed, but when a procedure outlined on this forum was followed, uninstalling Dot Net 2, 3, and 3.5, then reinstalling only Dot Net 2 from the Act CD, this problem was resolved. However, the slowness and window hanging has continued to adversely affect the users.

Any assistance much appreciated….
 
Hi,

Thank you for your response. Yes, the slowness also occurs on the server, and we can open the demo database without the issue repeating, but just to update you further, we have spoken to Sage support, and a couple of hours of checking Windows service pack levels and uninstalling / reinstalling client side did not resolve our problem with our database of 13,000 customers. They agreed that we did not have an unduly large amount of data though, even if their suggested solution was to upgrade.

Best regards, and thank you again..
 
Hi again

Thank you for your assistance. We used to have ACCPAC on this server, running on Pervasive SQL, but I stopped both services, relational & transactional, a year or two ago, when Act's SQL 2005 Express was installed, and they have been disabled for a while now, pre-dating this issue.

We have run the usual Act, Tools, Database Maintenance, Check and repair, which performs its Integrity Check successfully, then the Database Reindexes, and no erros in the Event viewer, with no change to the issue so far.

We have also installed SQL Server Management Studio [ SQLServer2005_SSMSEE.msi ] but to no avail, as there is no maintenance utility included with the Express Edition of SQL 2005, apparently. The external consultant that installed Act for the company used 2005 Express Edition, which I assume is usual practice.

As a result of lack of any obvious SQL based maintenance tools, we also tried ExpressMaint.exe, but we are unable to get it to attach to the Act7 SQL database. It quotes the error ' SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections ' but when checked under Surface Area Configuration tool, both local and remote connections are allowed. This failure to attach occurs both with and without the SQL database running in Single User mode.

Am I able to backup and restore the SQL version of this database to run it locally on a workstation.? I wasn't aware of that. Will look into that when back at work on Monday.

Thanks again..
 
ACT! 2007 and ACT! 2007 Premium Ex will only use SQL 2005 Express
ACT! 2007 Premium ST uses SQL 2005 Standard on server OS
ACT! 2008 Premium and later can use SQL Standard, even with Premium Ex.

Have you tried the database repair options with ACTDIAG:

Here are the instructions to move the database to test on a workstation:

Basically, you can then have an idea if it's a server issue or a database issue

Regards,
Mike Lazarus
ACT! Evangelist
GL Computing, Aust
Join the LinkedIN ACT! Fanatics Group
 
Hi Mike

Thanks for that. I will try both those ideas asap, I can try the repair option tonight remotely, after I've read up on it a bit, but the one where we move it to a workstation will have to wait till am back at that site, and I have a quick break planned starting this week. Its only for 10 days, but I won't make it back to them before I go away, so will have to carry on with this one after June 12th.

Will keep you informed, and thank you again for all your help.

Regards
 
Hi again

Sorry for the delay, work got in the way. Resolving all the issues to do with Act and Sage have been a long process but using techniques harvested from this and other forums we seem to have got rid of the appalling slowness that was making these programs practically unusable.

So basically, we have gone through a lot of the stuff mentioned above, ran diagnostics, cleaned and repaired Act, and think it was actually Sage causing most of the issue through the Act Sage Accounts Tab, where it shows a live view of the customers accounts info in Sage, in Act.

We ran the Sage diagnostics, cleaned and repaired its database, deleted a lot of old information, and it sped up considerably. Archiving the previous years were not an option at this time but will be run at end of year in a few months, so I might update this post then to advise everyone further at that point.

Also, we upgraded the switch and every network card in every PC to gigabit, and this has sped things up considerably as well. Ridiculous really tho isn't it, that a gigabit network infrastructure is required to run a pretty simple database on 5 pcs. Anyway, it is now 100 times better than it was, if not back to its original speed of a few years ago.

Thanks for all assistance,
Best regards

viewtopic.php?t=7959&highlight=slowing


Listed in order of significance [ maybe ]

A - Database Maintenance - HAVE NOT DONE ALL OF THIS

For gradual slowing another candidate is simple volume of data, e.g. audit trail size, plus areas such as the SOP and Invoicing Modules. If there are years worth of old orders and invoices in SOP and Invoicing then archiving some of those off, taking a few extra backups, deleting, then ReIndex & Compress might help. Try to avoid using Sage
as a long term archive. The obvious area of data that can grow very large is the audit trail, but large numbers of completed orders in SOP, posted invoices in the Invoicing Module, etc. can also significantly slow down a system.

The report module is also overly complex. Before it even displays the list of reports, it has to filter out the .SLY reports that have already been converted, and crazy stuff like that. You can speed this part up by manually deleting converted .SLY files and unused .reports.

In many cases, reducing the number of live transactions on the system - clearing out old invoices and transactions and relying on the data archive makes significant differences to the speed. Remember to compress data after clearing the audit trail.


B - Anti Virus - HAVE DONE THIS ON ALL PCS AND SERVER, NOT A MASSIVE DIFFERENCE.

One of the files that is opened and closed the most is queue.dta which is used to control record locking by users names. For a reasonable sized invoice this can be opened and closed 20 or more times. So if your AV scanner is set to monitor all files it will intervene every time this file is opened and re-scan the queue.dta file for viruses !
On a network, the whole problem is compounded because the operating system on the master PC is having to open and close files for all the PCs on your network, control the file locking and process anything that you may have running in the foreground on that PC, all at the same time.

Therefore, ensure all Sage directories are excluded from being checked when files are opened. If you monitor what Line 50 is doing with a utility such as Sysinternal's Filemon it reveals that Line 50 is constantly opening and closing the same data files many times a second. This is particularly noticeable when you post a screen full of
batch entries or an invoice with several product lines.

(Depending on what Anti Virus you use, you may want to completely un-install it from add/remove programs, reboot your machine and then try it again) You may want to check the server antivirus also as that could also be checking the files before they leave the server. Basically set Antivirus software on Client PC and Server to ignore
Sage folders, but at least the *.dta files (sage files) and specific folder (ACCDATA) and if able exlude all the followiign - add *.dta, *.coa, *.srt and *.sly to your antivirus exception list

C - Network Tweaking - HAVE NOT DONE THIS [ but have installed Gigabit speed gear ]

Ensure that the duplex on the network card is set to Auto on the speed / duplex option. If already set to Auto change to 100mbps Full Duplex or 1000mbps Full Duplex (if you have that option) If you believe it could be the network causing the issue, take a backup of your database
and install it locally on a single PC and then test it.

NB - Duplex can be somewhat less effective than they are advising, as different cards use Duplex in different ways and the wrong settings can make things worse, not better.

D - UNC Path - HAVE DONE THIS [ standard install ]
Do not use \\your-server\my-data ALWAYS use from a mapped drive i.e T:\my-data, UNC paths and Sage don't work well!


E - DNS Setup on Router - HAVE DONE THIS, CHECKED IT IS NOT THE CASE

The router might make a difference if it was set to give out a DNS address. This might affect Windows "Name resolution" which is how Windows finds things on the network. If not set correctly it would be asking your ISP where your Sage data files are before trying to find
them locally which would really slow things down


F - Updates - HAVE DONE THIS AS FAR AS ABLE AS OLD VERSIONS OF SAGE NOT THAT WELL SUPPORTED

Ensure your Sage product is fully up to date with all the latest service packs and hotfixes Installed SP1 as standard and no other hotfixes are available for version 12.

G - Unverified Tweaks - might be worth a try

= Add this line into the Sage.ini file on every client PC:
Files=100 ( Put this in the section "[SG50]" )

= try switching xp sp2 windows firewall off

= What is the transaction number you are on? If it is above 25000, it may be the file-locking that is a problem...

= You should check that "oplocks" has 'not' been turned off on the server or any of the workstations. By default it is on, however some multi user applications turn it off when they are installed. More info on oplocks here :
or Google "oplocks" or "Opportunistic Locking".

= Check the RAID array drivers. If you still have a drive in the server that is not part of the raid array you could try temporarily shifting the Sage data files on to it to see if it makes any difference to the problem. Can do this as the extra drives we put in the server are mirrored, not RAID 5, might be worth a try too if nothing else fixes this issue.


NB - You may find the following bit of software useful. It will show you all the file system requests that are being made when Line 50 is running. In advanced mode it also shows the FASTIO requests as well.

Freeware File Monitor from Sysinternals now Microsoft


When you first start the monitor up you should disable monitoring of your C: drive from the Volumes menu and just leave "Network" ticked. If you click the little clock tool bar button it will toggle the output between timestamping and showing the duration of each file operation.
 
Hi there

Thank you for your reply. ell, alright might be overstating it a bit ;0 but it suffices. We still have a few things to try to speed it up a bit, but on the subject of upgrading, will 2009 work with Sage 12 still.?

Cheers
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top