Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Connection String Settings: Timeouts and Pooling

 

If you are having SQL Server Database connection issues, I reckon they could be due to one of two connection string issues. Note that the connection string timeout is the 15s by default, so in the string below I triple it, for example, to 45 second (or on could try 30s as an incremental step), and secondly, if you do not want pooling you have to explicitly state you so not want it, as so:
 
If you are using Windows Authentication, or Integrated Security in Connection string lingo, here’s an example that disables Pooling:
Server=<SQL Server Instance>;Connection Timeout=45;Integrated Security=SSPI;
Pooling=false
Or if are using SQL Server authentication:
Server=<SQL Server Instance>;Connection Timeout=45;uid=;pwd=;Pooling=false

There is also the dynamic system view sys.dm_exec_connections , but I am intentionally brief on this since fellow MVP Glenn Berry has done such a great job already and recently on this within his DMV a day series J

 

 

 

Comments

Posted by cesardgo on 21 July 2014

Nice and works , but have this situation, if the server is alive and a set the connection time out to 5secs, the time out always is OK, but if the server shutdown or lost internet or Lan communication, etc ... always my app wait 21 secs to set the error. Is any way to force tcp response to 5 secs or equal to Connection string when my server is not alive ?

This is my  test code using vb.net ..

Dim Fecha1, Fecha2

       LblMensajes.Text = ""

       Fecha1 = Now

       Try

           Dim Cn As New SqlClient.SqlConnection

           Cn.ConnectionString = "Server=192.168.0.11;  Database=TestDB; uid=sa; pwd=sa; Connection Timeout=5;"

           LblInicio.Text = Now

           Cn.Open()

           Cn.Close()

           Fecha2 = Now

           LblFinal.Text = Now

           LblDiferencia.Text = DateDiff(DateInterval.Second, Fecha1, Fecha2) & ", Secs "

       Catch ex As Exception

           LblMensajes.Text = ex.Message

           Fecha2 = Now

           LblDiferencia.Text = DateDiff(DateInterval.Second, Fecha1, Fecha2) & ", Secs "

           LblFinal.Text = Now

       End Try

Leave a Comment

Please register or log in to leave a comment.