Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ColinD
Starting Member
40 Posts |
 Posted - 2007-10-15 : 04:56:41
|
| Hi,I'm not certain exactly where to post this. If this is not a suitable forum, please let me know. I have an odd problem with SQL, which has already been discussed under the Data Corruption forum at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90760. However it appears not to be a Data Corruption issue, so I wondered if anybody here could help. I'll try to keep this brief.I'm using SQL server 2000 as part of SBS 2003. I have two Front End applications written in Access 2003 which access the SQL BE. The first applciation is used by office staff, called the Job System, the second is used in the factory, and is called the Touch Screen system.These two applications have worked fine for at least 12 months. Both systems are linked to SQL using ODBC.On Monday 8th, due to a problem we had the previous Friday afternoon, I was forced to restore the database to 12:30pm Friday. Since then, everything has been fine, except that in one table, a bank of 15 records cannot be edited. There are 25,000+ records in the table, and all of the rest are fine and can be edited. If I try to edit one of the "bad" records, SQL times out or hangs indefinately. This is true if I edit the table direct, if I use SQL Analyser or if I use my FE application. There is no other error message. There are no triggers on the table.The 15 records can be viewed using Select, and can be viewed in my application, but as soon as you try to edit them, they hang indefinately or timeout.On Thursday night, at 9:30pm, with no other users on the system I checked the table and the records and I could edit them. Everything was fine. On Friday morning at 8:00am, (with only 3 users on the system) they hung again. Exactly the same happened the night before. Ok at 9:30pm, hung at 8am.The problem appears to be with our Touch Screen system. If there are no Touch screens logged on, the records are not locked. However, the Touch screens do not always lock the records. Sometimes the same action locks the records, sometimes it doesn't. The Touch screens access the server via a switch in the factory.Over the past week, with no other users on the system, I have tried the following:1) stopped and restarted SQL four or five times,2) I've backed up the database, deleted it and restored, 3) I've exported the table, deleted the original, created a new table and populated the new table from the export.4) I've rebooted the server,5) Last night I ran DBCC DBREINDEX (Jobs, '', 0) on the Live table and it completed OK with no errors,6) DBCC CHECKDB completes OK with no errors.7) I've rebooted every P/C which accesses the database.8) ran SQL Profiler to try to examine the problem.9) I've rebooted the netwrok switch in the factory.I logged in from home last night, to find that the whole table was locked, and the maintenance plan backup from Friday not completed. Only this table was locked, all of the other tables were fine. I restarted SQL and the table was released and the backup completed immediatlely. It had been locked for nearly 48 hours. This is the second consecutive night this has happened, yet it didn't happen for the first few nights.I'm really at a lose to know what to do now. Is it data corruption, my application locking the records, a network issue, an ODBC issue or something else? Any help or advice would be gratefully received. If you need any more information please let me know.ThanksColin |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-15 : 05:13:08
|
Have you tried PASSTHROUGH yet?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-15 : 05:20:53
|
quote:
Originally posted by Peso
Have you tried PASSTHROUGH yet?
E 12°55'05.25"N 56°04'39.16"
Sorry, I did reply to your message previously. I don't know how to do this, I need some more information. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-15 : 05:25:23
|
See if this helps.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-15 : 06:56:24
|
| Ok, thanks, I've created some PASSTHROUGH queries and am now experimenting. Can I ask what is probably a daft question - I have some "tables" in access, which are actually ODBC links to SQL views (i.e. NOT SQL tables). Do these behave in the same way as Access PASSTHROUGH queries? |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-17 : 03:53:45
|
Ok, it's possible that I've fixed this problem. Yesterday I blitzed the problem and threw the full weight of my knowledge and ideas at it (took about 10 minutes  ). There were no locks at all yesterday afternoon and the table was not locked when I came in this morning. Here's what I did:1) instead of using Access queries with SQL ODBC linked tables, I created Views in SQL and linked them to Access with ODBC, eliminating the need to use Access queries. I'm not sure if this is the same as creating PASSTHROUGH queries. I did try PASSTHROUGH, but it caused so many issues with my application (with subforms etc.), that I decided to put it on hold as a last resort.2) I archived and deleted 4000 records from the table which were entered prior to 2005.3) I refreshed all of the ODBC links in Access. 4) my new SQL views only select a subset of the data (i.e. records from the past 24 months on-going).I'm not sure exactly which one of these has "fixed" the problem, but I suspect no. 1, with maybe a bit of no.2. Can't see why it should be no. 2 though. Can't see why any of these should work actually, because it's been working fine for 12 months........Thanks for all of your help, I'll see how it goes,Colin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 04:27:44
|
| The view, as such, won't reduce locking etc. The FROM/JOIN tables in the View are "materialised" just as if they had been included in your query. The fact that the View references only a subset of the data may help localise the query, or possibly now means that there are few enough rows that Access grabs the lot, rather than leaving an open cursor for the remainder [which might have been what was leaving them locked before]if it has to do with the volume of "matching records" then (2) will help as well - reduce the number of eligible rows, and increase the chance that they all get retrieved by Access in one go.Is there any chance that some workstation [the faulty ones ideally!] have older MDAC version?Kristen |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-17 : 05:07:10
|
| Is there any chance that some workstation [the faulty ones ideally!] have older MDAC version?I dont think so. They all arrived at the same time and were all built from the same ghost.Still no problems..... |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-18 : 03:20:41
|
| So we've now gone another full day without any further locks, and everything was ok this morning. It does seem that the problem is fixed. I wonder if it will come back when we have added another 4000 records to the table..... |
|
|
|
|
|
|
|
|
Some records in table locking