MS Access Database Hangs Backend SQL Server

Our data resides in numerous MS SQL Server 2008 R2 databases. Data is accessed via various MS Access 2003 front end databases. The 2 most common are for managing customer demographic information and the other is for processing customer orders. We have approximately 15 to 20 users who are accessing this data at any given time. The front end databases are located on each user's workstation.

The problem: If one of the front end databases on a single workstation locks up, it takes down the entire SQL server and locks all other users out of the data. This occurs about twice per day on average. Some days not at all and other days more frequently. When this occurs, all other users get the spinning wheel of death and cannot access any data.

The lock ups have occurred on multiple computers and are usually limited to the 2 front end modules mentioned above. Sometime it is evident which computer caused the problem because an error message is displayed on the user's screen - usually a failed VBA routine, not always the same one, or an ODBC connection error. At other times, the culprit cannot be identified.

The problem can be corrected by closing the front end database that caused the problem. Upon doing so, all other computers free up and can then access the data. If the culprit cannot be identified, then by having all users close their open databases, the problem is corrected. There doesn't seem to be any commonality to the processes that are taking place on these workstations when the problem occurs. Both front end databases are large and contain a lot of functionality - they are 29MB and 25MB respectively after a compact and repair.

Any ideas?

Thinking that either database might be corrupt, I've tried compacting and repairing, and creating a new database container and importing the components. All to no avail.
LVL 3
Douglas CummingsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
It sounds like you are having locking issues in your SQL instance and that is probably what you need to keep an eye on.  next time it locks up run sp_who2 to see what processes are running and more importantly blocking the others.  I would recommend Googling and installing the sp_whoisactive procedure for more visibility into the active processes.

http://sqlblog.com/files/folders/release/tags/who+is+active/default.aspx

This is just a place to start.  You could have any number of other issues but you need to be looking at your SQL instance more so than the Access based on the symptoms you are describing.  When you close the front end database you are killing whatever connections it was maintaining and the associated transactions which may be causing the deadlock.  Please provide more information on your SQL Instance and how it is configured.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The problem: If one of the front end databases on a single workstation locks up, it takes down the entire SQL server and locks all other users out of the data. >>

 What it sounds like is the FE is executing queries that are just taking exceptionally long, your SQL Server is not up to the job, or possibly even both.

<<The problem can be corrected by closing the front end database that caused the problem. Upon doing so, all other computers free up and can then access the data. >>

  What you need to look at is what those users are doing.  What their getting I'm sure is ODBC timeouts.

  This has nothing to do with the FE's corrupting or anything like that.

There are a number of issues here:

1. Is the server up to the task?   Does it have enough free memory?   is it CPU bound?

2. Are the SQL databases being maintained?   Indexed properly?  Statistics being re-calculated.

3.  Are the queries in the FE optimal?   Are you making use of pass-through's and views?   Are you joining to local tables or using VBA expressions in them?  Are you using subqueries and / or Domain functions inside them?

4. Is the app designed for SQL Server?   by that I mean was it written for a true client/server setup, or is this an old converted JET app?

  I think you will find that #3 is the main culprit, and possibly #1, but that may simply be a result of #3.  

  It's like a snowball rolling down hill, it gets bigger and bigger as time goes on.

  As a first step, I would check the server's free memory.   Anything less then 20% is not good.   Beyond that, what the queries are doing that are used the most or the ones they get stuck on, and if proper indexing is in place to support those.

Jim.
Russell FoxDatabase DeveloperCommented:
It sounds to me like your Access users are causing deadlocks in the SQL backend. Next time it happens, open up Management Studio and run
EXEC sp_who2;

Open in new window

: look for SPIDs in the "BlkdBy" column to see who's blocking whom. If someone's blocking, say SPID 234, you can run
KILL 234;

Open in new window

to kill their session and hopefully release everyone else.

This is a temporary fix as you don't want to be constantly killing SPIDs. You may need to revisit your Access architecture: rather than having linked tables (which I suspect you have), you may need to have the users select the particular record they want to work on, return just that record from SQL, and then have code to save any updates back to SQL. Not a small project, so let's see if anyone else has better ideas.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Douglas CummingsAuthor Commented:
Thanks for all the responses.

In answer to some of Jim's questions:

1. I watched the server for a while and the CPU usage varied from 5 to 8%. Physical memory usage was in the 8 to 9 GB range out of a total of 32GB. There is 139GB free on the C partition.

2. SQL Server is not my strong suit. I believe that it is indexed properly, but I don't know what you mean by statistics being recalculated. So from that standpoint, I am probably not maintaining the SQL server properly.

3. I am making use of pass-through queries and views. However, see the answer to 4 below.

4. The databases were designed for Jet and not fully optimized for SQL server. Queries that deal with large record sets have been converted to pass-through and when I add features to these databases, I convert the associated queries to pass-through.

There are 2 or 3 computers on which this problem occurs more frequently. These computers are used for order entry, which I would regard as low rent. There is not much processing taking place - just the addition of new records to the customer table and the creation of a worksheet for subsequent order entry and processing. The only complication is that our customer table contains about 750,000 records. However, it doesn't seem to crash upon saving the customer record, but does upon creation of the worksheet.

This might be another clue: If I were to execute an update query that affects a large number of rows, usually maintenance related, and fail to commit the changes when the query finishes, it locks users out of the data until I do so.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK, couple comments back:

<<1. I watched the server for a while and the CPU usage varied from 5 to 8%. Physical memory usage was in the 8 to 9 GB range out of a total of 32GB. There is 139GB free on the C partition.>>
 
 Excellent, so no problems there.

<<3. I am making use of pass-through queries and views. However, see the answer to 4 below.

4. The databases were designed for Jet and not fully optimized for SQL server. Queries that deal with large record sets have been converted to pass-through and when I add features to these databases, I convert the associated queries to pass-through.>>

  Excellent again, your far better off than I was thinking<g>

<<2. SQL Server is not my strong suit. I believe that it is indexed properly, but I don't know what you mean by statistics being recalculated. So from that standpoint, I am probably not maintaining the SQL server properly.>>

  This is done in SQL Server as a maintenance job.   You want to re-compute statistics (this is what drives query costing plans) and reindex probably once a week.  We can tell you how to set that up, but that is likely not the direct problem, so let's leave it go for the moment.

<<The only complication is that our customer table contains about 750,000 records. However, it doesn't seem to crash upon saving the customer record, but does upon creation of the worksheet.>>

 Sounds like some indexing is in order, but a little more detail on that worksheet process please.  Is it based on a view?  joining local tables, etc.

<<This might be another clue: If I were to execute an update query that affects a large number of rows, usually maintenance related, and fail to commit the changes when the query finishes, it locks users out of the data until I do so. >>

  Well that's normal.  It's going to hold a lock on everything you touch, so the others were thinking in the right direction with locking.

  If the query is on the Access side and you can re-execute the query at will, then set the queries UseTransaction property to no.   This will flush all locks as soon as the lock limit is hit and then continue on.  And of course if at all possible, push these server side.

  If these are pass through queries already, then you really need to look at indexing.

  So a few more details so we can get a clearer picture and we can start getting down to some specifics.

Jim.
Douglas CummingsAuthor Commented:
The query that adds the worksheet is a simple Access append query with 1 table as its source, as follows:

INSERT INTO dbo_tblCurrentReturns ( CustomerId, SalesId, PaymentPreferenceId, ReturnStatusId, ShipViaId, DateEntered, DatePriced, BonusTypeId, AdvanceTypeId, PSPReferralId, PromotionId, UsernameCreate, FirstShipment )
SELECT dbo_tblCustomer.CustomerId, dbo_tblCustomer.SalesId, IIf([CustomerTypeId]=8 Or [CustomerTypeId]=9,4,18) AS Expr1, 11 AS Expr2, 155 AS Expr3, Date() AS Expr4, Date() AS Expr5, 7 AS Expr6, 1 AS Expr7, dbo_tblCustomer.PSPReferralId, IIf(IsNull(Forms!frmAddCustomer!PromotionId),0,Forms!frmAddCustomer!PromotionId) AS Expr8, fosusername() AS Expr9, IIf(IsNull(DLookUp("CustomerId","dbo_tblReturns","CustomerId = " & [CustomerId])) And IsNull(DLookUp("CustomerId","dbo_tblCurrentReturns","CustomerId = " & [CustomerId])),True,False) AS Expr10
FROM dbo_tblCustomer
WHERE (((dbo_tblCustomer.CustomerId)=[Forms]![frmAddCustomer]![CustomerId]));

However, as I was crafting this response, I may have identified the bottleneck. After a new customer is added to the database, I run a series of append queries that write potential duplicate records to a temporary table. If one or more duplicates are found, it opens a form to allow the user to select a match. The duplicate checking queries (6) include 2 soundex queries that compare first, last and business names to existing customers with the same city and state. I imagine that these queries put a strain on the SQL server. These are Access queries - not pass-through. All fields that are being check against each other are indexed.

Below is the syntax of one of these queries:

INSERT INTO tblTempPossibleCustomerMatch ( CustomerId )
SELECT dbo_tblCustomer.CustomerId
FROM dbo_tblCustomer
WHERE (((dbo_tblCustomer.CustomerId)<>[Forms]![frmAddCustomer]![CustomerId]) AND ((Soundex([FirstName]))=Soundex([Forms]![frmAddCustomer]![FirstName])) AND ((Soundex([Surname]))=soundex([Forms]![frmAddCustomer]![Surname])) AND ((dbo_tblCustomer.City)=[Forms]![frmAddCustomer]![City]) AND ((dbo_tblCustomer.State)=[Forms]![frmAddCustomer]![State]) AND ((dbo_tblCustomer.StatusId)<>5));
Douglas CummingsAuthor Commented:
I am off in a few minutes. I will revisit this question tomorrow morning. Thanks for the help so far.
Douglas CummingsAuthor Commented:
I am just getting back to revisiting this issue.

The duplicate checking doesn't seem to be the cause and I am not even sure that the creation of worksheets is the cause either. Yesterday was particularly bad in respect to lockups, eventually requiring a server restart to rectify the problem.

There have been no problems so far today, but it is just starting to get busy. It seems to happen when there are a lot of users (15 - 20)  working with the data simultaneously.

Another clue that might point back to the SQL Server is that seems to be fine for a day or 2 following a server shutdown.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sounds like you server is just over worked and what you need is SQL server tuning, then possibly basing Access queries on SQL views and/or using a stored procedure or two.

You'll need to use SQL Activity monitor to see what the issues are.   It may be locking (in which case it will show a process as being blocked) and if someone is taking an exceptionally long time to run something, what that is.

I would also be checking the indexing of the big tables and making sure that it seems appropriate.   You mentioned your customer table is 750,000 records and your doing a soundex on that; sounds ripe for a stored procedure.

Last, I would be setting up a SQL job to recompute statistics and reindex the tables just to cover the bases.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Russell FoxDatabase DeveloperCommented:
"Another clue that might point back to the SQL Server is that seems to be fine for a day or 2 following a server shutdown."

That sounds like the Access connections are just not getting closed. You can set a connection timeout or run a job late at night that kills all connections. This uses the undocumented sp_MSforeachdb to close everything: the proc will insert each database name in place of the "?":
EXECUTE master.sys.sp_MSforeachdb 'ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXECUTE master.sys.sp_MSforeachdb 'ALTER DATABASE ? SET MULTI_USER'

Open in new window

Douglas CummingsAuthor Commented:
Thanks for the suggestions. I will give both a try.

Russell, one question. What will this do to a user that leaves a database open on his/her computer? I ask because at least one Access front end database needs to process website requests in the evenings and on the weekends.
Russell FoxDatabase DeveloperCommented:
It would get disconnected with an on-screen error. You'd probably have to put up a "down for maintenance" page in the meantime. But it would only be for a few minutes.
Douglas CummingsAuthor Commented:
I setup a SQL job to re-compute statistics and re-index the tables last Friday. It may be too early to tell, but we haven't had a lockup since I did so. I will keep you posted.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Really believe this is something you incrementally worked into rather than any one thing being wrong or broken and  your going to need to slowly chip away at.

   Stats/re-indexing sounds like it helped some, now you need to focus on long running queries, and especially converting that Soundex Dup check to a stored procedure.  Again, don't forget that if any update/delete/insert query is re-executable without issue, then set the UseTransaction property to false.

 I think it's time to call this one and you'd be better served opening a new question on getting that soundex function into stored procedure if you need to, or anything else you might need help with working on.

Jim.
Douglas CummingsAuthor Commented:
Thanks Jim for all the help. By the way, day 3 without any lockups.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<By the way, day 3 without any lockups.>>

That's great to hear, but I don't think I'd take my eye off the ball quite yet.  Seems like it would be worth it to poke around at it for a bit yet.

Things have a tendency to keep getting bigger rather than smaller, so you may end up with issues before too long again.

Jim.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.