April 28, 2003 at 12:44 pm
We've actually already done that to prevent problems when a firewall is between the web and database servers...
April 28, 2003 at 2:07 pm
We seem to see this problem whenever the database reaches a larger size and tries to automatically expand to accept new data. Depending on the volume of new data, you might change the auto-expand to be a set number of MB instead of a percentage. You would have more control over the amount of time involved. Depends mostly on the speed of the machines. Our test machines are small and hit problems at about 1 GB.
Guarddata-
May 4, 2003 at 3:14 am
here is the solution of the Hang Problem between any program and SQL server 2000 or 7 🙂
When you want a report (a set of read-only data) which based on complicated queries joined from several tables, These tables are updated frequently, then you lock these tables (or you will wait for previous transaction lock on these tables to be completed to put your locks on them) .. so all other poeple using these tables are locked too (so hang happened - because they are in the queue of lock of these tables)!!
the best thing is to run the query on the server (for example: by calling a stored proceddure which reside on server, or by making Pass-Through Query in Access, or by writing SQL statment in your ASP or.. ) .. and with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other transaction will complete successfully (for you and others) 🙂 .
you will write something like this in your ASP query or in your program:
select Invoices.*, Situation.*
from Situation with (nolock) INNER JOIN Invoices with (nolock)
ON Situation.SituationID = Invoices.Situation
where Situation.SituationID =1
Disadv:
- when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.
Adv:
- no hang at all
- very fast Response
- Little summary for Locks in SQL Log file.
also : you may need to add more Memory (RAM) when server still hangs a little after transfering your queries to NO Lock queries.... becuase every transaction or query is done on server first, then processed data is passed to your PC.
I hope this help you All
if anyone needs any help, contact me.
bye
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply