March 24, 2002 at 5:10 pm
I have a front end (FE) database (Access 97) connected to a back end (BE) db in SQL Server 7.0.
The job gets done when the FE selects data from the BE using an Access query with criteria (year), performs some calculations and displays the results. This all works OK.
My problems begin when I try to import new data to the BE or delete data from it. I tried to run a trace on friday and it took me all day. I think the result is "Dead lock detection NCH1_BDC" (NCH1_BDC is the name of the server we are using).
At some stage in the past I tried to allocate 2,500,000 locks to the database. I recently found that that many locks could not be allocated and the server reverted to ???? (less then 1,000 anyway). I deal with large amounts of data and I think this is the problem (or part of it).
I am using Access queries to import/delete the data on the SQL Server database.
Can anybody pls assist?
thanks Dave
March 24, 2002 at 6:20 pm
Access isn't always very efficient. Whenever possible replace Access queries with SQL stored procedures or views. For importing data, your best bet is to try DTS. Typically imports will go into a "staging" table and then appended in one shot from there. In general you want to do as much of the data management on the server as you can, use the front end to do the graphical interactive part.
Allocating more locks is almost never the right strategy. Tracking deadlocks can be hard - you need to turn on trace flag 1204, wait for it to happen, then inspect the error log to get an idea of where it's occurring. If you can get that and post it, we may can offer some more help with resolving it - there is a lot to it!
Andy
March 25, 2002 at 5:37 am
Also I would do a trace on all the stages of the transactions from start to complete and see if a specific step is occurring when this happens since you seem to know how to recreate from Access. And I agree with Andy Access is a bad choice for front ends unless you use the VBA stuff and build ADO connected apps use Access as the application framework and not engine.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 25, 2002 at 3:58 pm
thanks for your responses Andy and Antares686.
Andy, I have to go and learn how to do a specific trace (flag 1204???) and how to see the errors log. Oh boy, this is going to take a while isn't it.
Antares686, I thought I did run a trace on all stages of the transaction on Fri but it hang up when it displayed "Dead lock detection NCH1_BDC" which I found on Mon.
I really don't know what I am doing.
thanks
Dave
March 25, 2002 at 4:08 pm
One additional bit of info:
the data consisted of 3 year periods:
The first lot was from a table created in Access and exported to SQL Server,
The second lot was imported from Access using a query,
The third lot was also imported using an Access query.
The trouble started when I had to delete the 3rd lot which I did with a lot of difficulties.
Now that I have to re-import this 3rd lot I find that I cannot do it.
I found it strange that I could do the imports initially but not now.
Dave
March 25, 2002 at 6:20 pm
Have you changed your index plan? Deadlocks often center around index access - if you've got an index that is heavily used (and thus effective) trying to add a bunch of rows may result in the index being locked...or attempted to be locked anyway. Have you tried doing the import at a time when no users are in the db? Not uncommon at all to remove indexes when importing large amounts of data and then add them back when done. In your case a simple workaround would be to import during the day to a separate table with no indexes, then schedule a job to do the insert after hours.
Andy
March 26, 2002 at 6:10 am
If the indexes cannot be dropped you may want to try an index rebuild and reset the fill factor so there is space in the index files to accomadate the incoming data without causig page splits and data movement on the indexes themselves from being a large issue. I usually rebuild mine once a week and set fill factor to 75-90% for each page.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 26, 2002 at 5:07 pm
thanks guys,
you got me to think that maybe the index of my main table is not as good as it could be. I got a number of fields (I think about 8 or whatever is the max allowed) to form the unique index. This is probably wrong.
thanks again, I will look into your suggestions and see what happens.
Dave
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply