October 6, 2003 at 4:33 am
Hi Experts,
We are using connection pooling of initial 10 connection pool in J2EE weblogic 6.1 environment and using mssqlserver type 4 for connecting to sql server 2000 database.Sometimes we are getting following error -
mssqlserver4.TdsException - I/O exception while talking to server
My question is - Is there something like idle connection timeout or invalidating connection from sql server 2000 database?
Pl. let me know in detail the configuration of connection timeout at database.
TIA,
Sheilesh
October 6, 2003 at 6:10 am
If this is really a timeout issue, check the documentation of the J2EE connection pool and mssqlserver4 objects.
I do not believe SQL Server closes any connection automatically, unless you set the server to single-user mode or issue the DISCONNECT command.
October 6, 2003 at 6:37 am
Thanx NPeeters , I am very much agree with u and also believes same , but trying to get more info on this , i may be missing something on this.
October 7, 2003 at 8:39 am
here is the solution of the Hang Problem between any program and SQL server 2000 or 7 🙂
When you want a report 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 (by making Pass-Through Query in Access for example, or called a stroed procedure on SQL Server) .. 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 Pass-Through Query in Access:
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 path-through queries.... becuase every transaction or query is done on server first, then processed data is passed to your PC.
ALSO:
try to put the DATA and the LOG file of SQL Database on a partition that is Formatted with 32 K or 64 K Cluster Size (not the defalut = 4 K)
because the default Page size in SQL in memory is 8K ,
And If u use the defulat Cluster Size while formating partioin, then to get 1 page (8K), you need 2 hits for the HardDisk (4 K each)
But If u use 32 K Cluster Size while formatiing partioin, then on 1 hit for the HardDisk (32 K each) , you get 4 pages into Memory (more faster ... because the slowest thing that slow the whole system is to read/write from Hard Disk)
I use 32 K not 64 K.. because with 64 K you may have a lot of empty pages in memory ( lost in memeory and your DB may be graw more)
this of course will help you to solve the problem of hang more and more .. bye
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply