unexplained timeout cured by renaming table
-
Newbie with a problem - sorry...
SQL 2000 (sp4) running on Windows Server 2000.
Clients connecting via RDO, performing inserts (using a stored procedure) and updates (direct sql statement) to a number of tables periodically get a timeout across the board. Timeout set to 30 seconds. Other users inserting to or updating different tables are unaffected.
I can't see any locks on the tables/databases in question in enterprise manager. The only resolution I have found so far is to stop all user activity, rename the table and then rename it back to the original name. The first of these usually takes some time to execute, suggesting to me that there are some uncommitted transactions that are locking the table preventing the inserts/updates from being handled.
Anyone seen this before or got any suggestions how to proceed?
Thanks.
-
1. I would switch the application to use ADO v. RDO
2. I would change the UPDATE portion of your equation to use stored-procedure v. AD-HOC query. Your timeout may be due to network congestion, etc...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Why? You don't know if the problem is RDO related and are you saying ADO is without its problems?
-
Renaming the table is one of those things that works, but probably not why you think it is. I could be wrong, but my bet is that it works because in the process of renaming the table, you are forcing all users out, which lightens the load on the server. In essence, its kinda like re-booting. You could probably gain the same effect simply by forcing the users out, and letting them come back in. Not a solution, but lets you accomplish the same thing without making changes to the database.
Another thing that would probably alleviate the problem would be to throw more memory at it. Add memory to the server, and the problem may go away. Again, its not really a cure, just treating the symptom.
Your best bet, like AJ said, is to fix the code.
Steve
-
I suggested moving to ADO from RDO because RDO is no longer supported or recommended. ADO on the other hand is still the connection of choice (for now at least) and is supported by the latest MDACs.
I understand that if the application isn't broke don't fix it (Highly believe in it), However... I think that if you change the UPDATE to be stored-procedure driven your problems MAY go away.
RDO > ADO isn't needed but is something to think about as a future enhancement.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Thanks all.
Changing app to ADO isn't an option as it's a third party app - no longer supported or in development. RDO was used (I'm told) because of backward compatibility with legacy systems...
The problem has also manifested in a database where the insert/update is handled in a stored procedure.
It's the wierdest thing I've come across in all my years working on SQL server.
-
bummer....
I would check sp_who2, sp_lock, and possibly run profiler to see if you can capture what is going on....
Good Hunting!AJ Ahrens
webmaster@kritter.net
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply