April 13, 2007 at 5:57 am
Hello:
I've been having a prolem with my SQL Server 2000, in my company we have 3 systems working on SQL Server 2000 one is a Time and Attendance System, Payroll and HR system and Coffee shop system, but the problem is that recently in random days and hours i've been experiencing problems with connections, the programs lost connections with the server and stop working, can somebody tell me why could be this? thanks, the server does not send any message on the event viewer or something related with the lost connection.
April 13, 2007 at 8:34 am
Did you check the connection setting under the SQL Server Properties dialog?
Check the maximum connections and timeout.
April 13, 2007 at 9:18 am
Ok, i checked it out and in connections is unlimited but in query timeout is 600 the value this server supports 3 systems and 4 databases.
April 13, 2007 at 9:29 am
Actually this could be pointing to a connection issue related to number of open connections. Remember each connection takes roughly 50k to 100k of memory when created, check the number of open connection the next time a failure occurrs. If you have a lot then you might need to check the code (if you can) to make sure connections are being closed as soon as the process is complete and not left open. If left open the connection will not be reused and instead a new connection is created for the application which after a period of doing this adds up to a crash.
April 13, 2007 at 9:33 am
yes, actually i saw that in the time and attendance system there's an open connection all days cause the system open a connection when starts and never close the connection it's open 24/7 maybe that's causing tue connection issue and the coffee shop system works in the same way i will rewrite code this porgrams were working in that way when i start working here.
Thanks i will try changing the code.
April 13, 2007 at 9:44 am
ok let me see
i have 7 clocks for time and attendance system, 15 Pc using payroll and HR system and 1 pc using coffee shop system, every PC or clock opens the connection when the system start and never close the connection, do you think that this might be causing the problem?, i mean there are like 25 open connections all day long and sometimes when the time and attendance clocks are rebooted the program did not close the connection. what do you think about this. they never had this problem before because there were only 1 system using SQL but now there are more systems working on the same server.
Thanks
April 13, 2007 at 9:53 am
Could be but what I refer to is that say for instance you have these 25 connections (which could be pooled btw way so don't assume it is actively open and in use), but later you have 50 as new connections are opened but never closed, then 75 then 100 and so on.
Code usually looks like this
var varConnection = new adoConnectionObject
(setup connection and using objects such as recordsets or command objects)...
varConnection.Open
(code to perform action on open connection)...
(destroy objects other than connection)...
now this looks fine but you forgot to issue
varConnection.Close
because of this it is left open and when Connection is created again and opened at another time it has to create a new connection cannot use the existing one. If you have closed (not destroyed) the active connection is avaialble still except it exists in the connection pool. When another piece of code calls the same connection (doesn't have to match exactly btw) it will use the available pooled connection instead of creating a new one. Note the pooled connection is still connected to the SQL Server just not not set to active and in use.
April 13, 2007 at 10:13 am
Oh ok i understand but what will happen if for example in the company there are like 2000 employees so they check in the time and attendance system at same time every day if i set the program to open and close connection between every time an employee check in the clock, what could happen if two programs try to open the connection at the same time? just to know if i could have problems with this...
Thanks
April 13, 2007 at 10:24 am
In that scenario a new connection will be generated for all concurrent needs. So if all 200 hit at the same exact moment you get 2000 connections but say your code is web based and each page opens a connection and doesn't close, if the work is 10 pages long and you have 2000 concurrent hits all 10 times you end up with 20000 connections open instead of 2000. To address the potential of 2000 concurrent hits you might want to have you connections timeout after a period so they are dropped from the pool automatically or you might want to address you hardware as you may have under estimated your needs.
April 13, 2007 at 1:38 pm
mm i think i dont understand let me explain you in the program code when the form is load the program opens the connection, example:
form_load
conn="connectionstring"
conn.open
end sub
and then every process uses this connection to do any operations, and never close the connection, until the program is ended now i was thinking in this
btnpress_click
conn.open
try
process to do
conn.close
catch
conn.close
end try
end sub
in that way every time the user push the button the system will open the conection and when finish the process will close the connection.
wich way is better for you?
April 13, 2007 at 1:58 pm
btnpress_click
conn.open
try
(process to do)
Catch ex As Exception
finally
(check conn state to make sure not closed already for cleaner exit)
conn.close
end try
end sub (btnpress_click)
Use finally (which vb.net does not add for you) to do cleanup that you want to execute regardless of error. But yes this would only open and utilize the connection when not in use. Is this a windows application or web application BTW?
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply