SQL 2005 Index Locks with ACCPAC 6.0

[ZA0431]

I have a query that I am sure should interest someone.... !

We have an SQL 2005 Server Cluster environment hosting multiple ACCPAC databases. We access ACCPAC through a number of Terminal Server farms. I have farms configured ACCPAC 5.5 and ACCPAC 6.0. Server's are over specced from a processes, memory, CPU etc perspective. All idle at under 60% usage.

Our issue is that we experience index level locks twice a day on our ACCPAC 6.0 databases. ACCPAC 5.5 hosted on the same environment has no locks.

After much investigation I have found that the locks seem to happen when there are OE prepayments captured on an invoice. The procedure calls from 6.0 are different from 5.5 in that there are verification procedures and the entire index is being locked on a batch for tables as part of the procedure.

One solution I have thought about is to change index locking on SQL to lock at page or row level rather than at table level. Am not sure how ACCPAC will react to this? Would I be compromising data integrity?

My next solution.... Find someone who has a solution!!! I haven't found any similiar experiences on Google. Anybosy have any ideas?

 

[Jay Converse]

1. Accpac service packs?
2. Which ODBC - Native client or classic?

 

[ZA0431]

Hi Tuba

1. All the latest sp's loaded.
2. Using ODBC native client.

Thanks!

 

[DjangMan]

Are there locks being placed on the table or is SQL doing a table scan which will lock up the table?

 

[ZA0431]

Hi DjangMan

The locks are application locks on the index. Have monitored SQL and there aren't any processes running from SQL creating table locks.

 

[ramam1]

You are sure they aren't from index maintenance operations on the server? Depending on your server version and the job being done it is possible that mssql is locking the index. See

http://technet.microsoft.com/library/Cc966402