Random Shared data issue??

[DawieNel2]

We are currently running ACCPAC 6.0 PU2 (125 users).

There are 7 Terminal Servers (Win Serv 2008 R2 64bit) and 1 Citrix server (Win Serv 2003 R2 32bit)

Database is on Win Serv 2012 R12 64 bit with SAN running SQL 2014. Shared data is currently on a pure storage device (SSD) on the same SQL server however has been moved around from a share on one of the TS boxes, a share on another random server purely used for storage, as well as a RAM drive yet nothing seems to permanently resolve the problem.

There are a couple of intermittent issues and we suspect it has all to do with the shared data?

The first is a head blocker which occurs in SQL when a user creates an AR Inv batch. It's a fairly "busy" system with quite a number of users working simultaneously and this will then lock up the DB until we kill the process in SQL. The AR batch will then be created however the user gets an error and when they exit the batch listing and go back in, the new batch is there and they are able to proceed working in the batch.

The other issue is that, when logging in, users will get the "cannot verify user info" error as if the system cannot read the shared data. When this happens, one can go directly to the share from the same session and create and delete folders so the share is there and available however suspect there must be some file locking taking place. The problem will often go away by itself in a minute or two.

Backups run after hours so doubt this could be the cause, perhaps anti virus?

Also, must opportunistic locking be turned off on both the server hosting the shared data as well as the clients, as our IT reckons server side is sufficient?

For what it's worth - we also seem to have fairly high async_network_io waits on the ACCPAC database.

Any input / thoughts would be appreciated.

Thanks
Dawie

 

[Jay Converse]

Get better hardware, it's overloaded. And make sure the databases are Simple recovery, not Full.

 

[DawieNel2]

Server seems to be idling and DB is in Simple recovery.

Rather suspect a locking problem on the Shared data (semaphore.bin and/or users.ism)but not sure what could be causing this?

 

[Jay Converse]

Semaphor.bin and users.ism indeed use old technology, as in Accpac for DOS old. But I haven't heard the phrase "opportunistic file locking" in this context in years. See this thread:

http://www.pluscommunity.com/ubbthreads/ubbthreads.php?ubb=showflat&Number=15698&fpart=2

 

[DjangMan]

It might be time to consider purging some historical data, too. With 125 users you're going to be generating a lot of data. If better hardware doesn't work or is out of the question then making a copy of the database and purging historical data can reduce the amount of data that the database is being asked to read when performing operations.

An easy thing to try is some maintenance on your SQL database, too. This site has a great script that makes maintaining your SQL indexes easy.

https://ola.hallengren.com/

 

[ettienne]

Something is bad in your configuration, those are some unusual errors. Are you working with a local Sage business partner, if so what do they say?

Sage 300 Certified Consultant

 

[DawieNel2]

Thanks for replies...

We are busy with a history clearing process and can understand that big data could be causing DB locks within SQL as well as async_network_io waits however can't see it having an impact on users.ism?

 

[Jay Converse]

Clearing history won't change anything. Reports are slower with lots of history, but daily transactions are not. And, the files in the SITE folder are not affected by anything in SQL. I'd focus on the SAN configuration.

 

[DjangMan]

It could be that your .ism files are corrupted. Have you run the scanisam tool?

Also - are there any other types of backups running? Like server image backups?

Make sure that the workstation anti-virus software is not scanning the shared data items. Let the server handle that.

 

[DawieNel2]

Scan Isam reports no problems and backups only run after hours whilst we experience this during working hours.

Also, the DB resides on the SAN, which hosts other DB's as well, whereas the shared data, and my gut tells me this is causing the problems, is on a separate PureStorage SSD.

Will chat to IT wrt antivirus but what makes this frustrating, is the fact that it happens intermittently

 

[ettienne]

It is not an issue with the shared data files - they do not influence SQL at all.
I suspect it relates to the SAN and/or network config. But this is going to be difficult to diagnose from snippets of information posted in a forum.
You have something unusual going on in your environment.

Sage 300 Certified Consultant

 

[DawieNel2]

Hi Ettienne,

Why do you recon it's not shared data?

Surely the "cannot verify user info" error at login relates to the system not being able to read the users.ism file?

Also, can someone perhaps explain the function of the semaphore.bin file. My understanding is that ACCPAC uses it to establish who is doing what in ACCPAC, therefore, if this file cannot be accessed for whatever reason, would this not impact on ACCPAC's ability to manage access to the database and/or tables, etc?

 

[ettienne]

20+ year's of experience.

You have multiple issues going, bad configuration or network components. As I said before this is not something that can be diagnosed/fixed from snippets of information on a forum.
Get a professional to resolve it. If you need a reference to a good company then I can give one boet.

Sage 300 Certified Consultant

 

[Jay Converse]

Ditto here, add my 20 years of experience to Ettienne's. You have a misconfigured network.