We have several customers running our MS Access 2002 front end on SQL Server 2005 - Free version on a Win 2003 server. From Time to time certain tables/records get locks that prevent users from using them. We cannot find a pattern to this although it is always certain tables. When I look at the activity log there are no active locks, and no blocks. If we down SQL server and bring it back up everything works fine.
It appears to be worse when there are a lot of users, but not neccessarily more user in the tables that have a problem.
Our customers running exactly the same code on an Accesss .mdb backend do not have this issue. When looking at activity monitor there is never any blocking shown. Almost never any active locks. There are usually 50 or 60 Sleeping processes awaiting a command. (This is with probably about 10-20 users logged in)
One thing that is very strange that I do not understand. There will be activity for Host computers that are not even logged in to the network and haven't been for several hours. I know for a fact that these computers were shut down cleanly. Why would SQL maintain processes for computers that are not even on the network, much less logged in to SQL server?
I know this message may need further explaination. I am by no means an SQL Server expert but I am trying to learn. Let me know if you need more data.
Thanks for your time