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-10 : 06:00:22
|
| Hi,I am using SQL Server 2000 as part of SBS 2003. I have an odd problem with a table in my database.On Monday, due to a problem we had on 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.As far as I can see, the id's of all of the bad records are the same as those which would have been entered on Friday afternoon, but which were lost with the restore. Records before and after the bank of 15 can be edited ok.Last night, with no other users on the system, I exported the table, deleted the original, created a new table with the same name and fields as the original and used Analyser to populate the new table from the export. I then tried to edit the bad records and it worked fine. However, this morning, with other users on, it fails again and the records cannot be edited.I have used DBCC CHECKDB and it displays no errors.Can anybody suggest what this might be and how I can get round it?Thanks for any helpColin |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-10 : 06:39:09
|
| "I have used DBCC CHECKDB and it displays no errors."I presume that was BEFORE you did the Export / re-import?If not suggest you run again to check for "damage".Might be that the Index / Statistics are out of shape. Try a Reindex and see if that helps.Other than that something may be blocking those records.Kristen |
 |
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-10 : 07:07:37
|
| I thought it might be an index problem, that's why I re-created the table last night. However, if that is the case, I'm not sure why they would edit ok last night but not today. How do I re-index?I just ran DBCC CHECKDB again, and once more it displays no errors.Thanks for your helpColin |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-10 : 07:26:11
|
quote:
Originally posted by ColinD
I thought it might be an index problem, that's why I re-created the table last night. However, if that is the case, I'm not sure why they would edit ok last night but not today. How do I re-index?
To answer my own question, I just ran the following on the test system and then on live:DBCC DBREINDEX (Jobs, '', 0)It worked fine on Test and came back successfully within 15 seconds or less. On Live, it timed out. Does this indicate a problem with the index, or just that people are using it and I can't do it right now? ThanksColin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-10 : 09:48:35
|
| I reckon its blocking.Are LIVE and TEST database on the same server, or different servers?Worth checking the Event Log on Live Server in case any hardware errors ...... network card / cable failing is a possibilityKristen |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-10 : 10:00:33
|
| They're both on the same server. No hardware errors being reported.What do you mean by blocking?ThanksColin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-10 : 13:05:35
|
| "What do you mean by blocking?"e.g. some query or update has locked the table for extended period of time, and that lock is "blocking" other users attempts to query the table.When it happens again you could try:SELECT *FROM MYTable WITH (NOLOCK)WHERE MyPK = 1234to try to retrieve one of the individual records you know is timing out.If that works OK try again without the "WITH (NOLOCK)" and if that hangs I reckon its a lock for sure.Might be that an application is doing a BEGIN TRANSACTION and then updating something and then crashing or taking-its-time before doing a COMMIT. (Very bad practice to issue Transaction from Application in this way, but it might explain the problem)Kristen |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-11 : 03:34:34
|
| Thanks for your advice. The select works both with the Nolock and without.Let me try to summerise the situation, as much for me, because I can't really believe what is happening.A bank of 15 records cannot be edited in a table...... 25,000 records before them are OK, and 200 which have been entered after them are also OK. 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.Last 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. This morning at 8:00am, (with only 3 users on the system) they hang again. Exactly the same happened the night before. Ok at 9:30pm, hung at 8am.Over the past three nights, 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.Why does it allow edits at 9:30pm, but not at 8:00pm and during the rest of the day? Why is it only 15 records in the middle of a table of 25,000 records? It defies logic..... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 03:49:07
|
| "This morning at 8:00am, (with only 3 users on the system) they hang again"if you can easily get the three users to exit the application I would try that.Try editing the recordlet one user in, try again.Add a second user ...This sounds to me like some sort of lock being help by the application. If might just be that the 15 records are clustered around something that the application is holding a lock on (so the users don;t have to be editing that record, per se.)We write our applications lock-free, so I'm not much good with diagnosing this type of thing, I'll see if the other regulars have any ideas.Kristen |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-11 : 05:38:01
|
| Kristen,I followed your advice, and the problem appears to be with our Touch screen system.Our SQL database is used by two different sets of users. Both sets of users use MS Access front ends. Most users are in our office and use our Job System, but we have 16 touch screens in the factory which use a different FE. However, both systems use the same SQL BE. The touch screens are set up in an identical way, using a ghost image, but everytime they logon, they use a logon script to check for and copy any updates to the FE, so they should all be using the same version of the FE. I am the only person able to make the updates to the FE. The Touch screens are used 7 days a week and I have not modified the application for at least two months.It appears that on SOME touch screens (but not all), as soon as they logon, they lock the bank of 15 bad records. These Touch screens are not accessing the records themselves, but do access the table, though they NEVER write to the table.At first I thought that it was just one rogue touch screen causing the problem, but I've now found another two. If I run the Touch screen application from my P/C and exactly replicate what the operator did on one of the rogue touch screens, my p/c does not hang the "bad" records, but it did on the touch screen.I can now replicate the problem by turning the rogue touch screens on, and fix it by turning them off.This can surely only be a network issue. I did reboot the factory switch yesterday, so it must be more than just that, and it doesn't explain to me why it would be the same bank of 15 records which are effected everytime.Thanks again for all of your advice. Sorry if this is getting boring now,Colin |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-11 : 05:41:25
|
acctually this is getting interesting!!!!there's of course no way we could even predict something like that  do post follow ups!_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-11 : 05:50:40
|
| well if you can replicate the problem start up the profiler and see what is going on._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-11 : 06:24:05
|
If you have MS Access as intermediate storage for SQL Server, whenever you "edit" and do not leave that record, it is still place a lock on the underlying table (which is sql server I presume).You could try changing the connection type in MS Access to "PASS THROUGH" and see if there is a difference.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 06:48:03
|
"I can now replicate the problem by turning the rogue touch screens on, and fix it by turning them off."Excellent! I recommend buying some new touch screens for the faulty ones, and giving me the faulty ones "If I run the Touch screen application from my P/C and exactly replicate what the operator did on one of the rogue touch screens, my p/c does not hang the "bad" records"Did that test include using the operators login credentials, and any other "environmental" settings that PC would have [which might influence the Application]?Now you have repeatable circumstances would be worth trying (presumably after-hours) turning on one "bad screen" whilst logging the queries with SQL Profiler.And then repeat with a "good screen" and seeing what looks different.(If you do this during the working day I doubt you will be able to see the wood-for-the-trees in SQL Profiler output)Kristen |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-11 : 07:06:55
|
quote:
Originally posted by Kristen
"I can now replicate the problem by turning the rogue touch screens on, and fix it by turning them off."Excellent! I recommend buying some new touch screens for the faulty ones, and giving me the faulty ones
Don't tempt me  quote:
"If I run the Touch screen application from my P/C and exactly replicate what the operator did on one of the rogue touch screens, my p/c does not hang the "bad" records"Did that test include using the operators login credentials, and any other "environmental" settings that PC would have [which might influence the Application]?
No, it didn't. I've just brought a rogue touch screen to my desk. I'll try it from here, because this will take out the factory network / switch etc.quote:
Now you have repeatable circumstances would be worth trying (presumably after-hours) turning on one "bad screen" whilst logging the queries with SQL Profiler.And then repeat with a "good screen" and seeing what looks different.(If you do this during the working day I doubt you will be able to see the wood-for-the-trees in SQL Profiler output)
Yes I was thinking along similar lines myself. Not sure it will be tonight though, because we're on "Team bonding" night out with the directors and management team. Maybe I'm no longer invited though.... |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-11 : 11:34:06
|
| Just a bit more information. The 15 records are currently in the lock state. I'm in Enterprise Manager, Management, Current Activity, Locks / Object, JobSystem.dbo.Jobs. The right hand window has a number of locks, which include the following:Process ID = 55Lock Type = PAGMode = SStatus = GRANTOwner = XactIndex = PK_JobsResource = 1:1866There are 3 locks identical to this, except they have different Process IDs. They all have the same resource. The three process IDs all refer to Touch screens, even though the touch screens don't actually update the records, they simply view them.When I look at these Process IDs in Process info, they all have a status of runnable, a wait type of NETWORKIO.I'm not sure if any of this is relevant.ThanksColin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 15:22:19
|
| "[i]I'm not sure if any of this is relevant[i]"I'll put money on it!Access is, for some reason, locking these records in order to view them.I can't guess why, but the thought occurring to me is that this is "just over" some threshold and Access has issued a Read Lock to get a consistent view, and then not actually read the rows, or failed to release the Lock ['coz it doesn't think its done with them yet, or "Whatever,do-you-think-I'm-bothered" [(c) Catherine Tate]Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-12 : 01:59:19
|
See post made 10/11/2007 : 06:24:05 for possible solution.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 02:32:44
|
| Indeed, it will most probably be something like that.Bit of a struggle to see why that would effect such a localised set of rows.I wonder:"the id's of all of the bad records are the same as those which would have been entered on Friday afternoon, but which were lost with the restore"Maybe the restore took away some referential integrity? Maybe there is some data that references the missing records, goes looking for them, doesn't find them, that data-reader is left open, program errors, catches the error [so no on-screen error message] ... Having a look with SQL Profiler should show a transaction started on one of the offending screens, and then not completing.Kristen |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-10-12 : 03:35:36
|
quote:
Originally posted by Peso
If you have MS Access as intermediate storage for SQL Server, whenever you "edit" and do not leave that record, it is still place a lock on the underlying table (which is sql server I presume).You could try changing the connection type in MS Access to "PASS THROUGH" and see if there is a difference.
HiRemember, the Touch screens never edit the table which locks, they just view it. I wrote the application, so I can be confident of that at least.Something I should have mentioned at the start is that the Access FE connects to SQL using ODBC. Bearing this in mind, is it still possible to change the connection type to "Pass through"?I'm now very much on the edge of my knowledge of SQL, so please excuse my ignorance of some of the suggestions. I came in this morning to find that the whole table was locked, with just one Touch Screen logged on and locking the table. I stopped and started SQL, and that released the table. Later, when I was checking the backups, I noticed that last nights maintenance plan only completed at 7:17am, in otherwords immediately after I restarted SQL. Normally it completes at 11pm, so presumably the table had been locked all night.Thanks for all of the help on this, I really appreciate it,Colin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 03:53:51
|
| "Remember, the Touch screens never edit the table which locks, they just view it. I wrote the application, so I can be confident of that at least."hehehe ... but what if in reading it Access has locked the records (in CASE they are edited) and then for some reason that application has not closed that connection/data-reader/whatever?You need to fire up that SQL Profiler ... you can probably put a filter on it for a specific workstation, so you may be able to trap the errant stuff even with all users busy working normally.What part of UK are you in? (me@Suffolk, you@JustDownTheRoad? )Kristen |
|
|
|
Next Page
|
|
|
|
|
Bank of 15 records cannot be edited in a table