Connection leak , its symptoms and troubleshooting
To know about connection leak you first need to have a basic idea of connection pooling. If you are not sure about connection pooling please visit the following link –:
https://msdn.microsoft.com/en-us/library/ms810829.aspx
https://msdn.microsoft.com/en-us/library/8xx3tyca(vs.71).aspx
https://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;191572
Connection leak basically happens when we open a connection to the database from our application and forget to close it, or for some reasons it doesn’t get closed.
To implement connection pooling we basically use these connection string properties –;
1) Connection Lifetime (Default 0) - When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online. A value of zero (0) will cause pooled connections to have the maximum time-out.
2) Pooling (Default true) -; When true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool.
3) Max Pool Size (Default 100 ) -:The maximum number of connections allowed in the pool.
4) Min Pool Size (Default 0 ) -:The minimum number of connections allowed in the pool.
5) Connect Timeout or Connection Timeout (Default 15) -: The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim cn As New SqlConnection("Integrated Security=SSPI;pooling=true;max pool size=2;Persist Security Info=False;Initial Catalog=master;Data Source=YOUR_DB_NAME")
cn.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
In the code above we are opening a connection to a SQL server but not closing it. As a result when this event is triggered third time, we get a error message , as we run out of connections in connection pool. If we simply put a “cn.close” statement in the above code after opening the connection it will never throw an error.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim cn As New SqlConnection("Integrated Security=SSPI;pooling=true;max pool size=2;Persist Security Info=False;Initial Catalog=master;Data Source=YOUR_DB_NAME")
Dim i As Integer
Dim a As Integer = 0
cn.Open()
i = 5 / a
cn.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Now look at this code. Even though we are closing the connection , but still we are facing a connection leak. The reason is that the cn.close is never getting executed as the link above it is throwing an error and the handle is passed over to the exception handling section. This kind of scenario is quite common when the developer thinks that he is closing the connection but still faces a connection leak issue.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("Integrated Security=SSPI;pooling=true;max pool size=2;Persist Security Info=False;Initial Catalog=master;Data Source=YOUR_DB_NAME")
Try
Dim i As Integer
Dim a As Integer = 0
cn.Open()
i = 5 / a
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
To stop this from happening the best thing to do is to close the connection in the “Finally” section like the example above.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Using cn As New SqlConnection("Integrated Security=SSPI;pooling=true;max pool size=2;Persist Security Info=False;Initial Catalog=master;Data Source=YOUR_DB_NAME")
cn.Open()
End Using
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
The code above is another good way to avoid connection leak. Because in the code above the compiler implicitly calls a cn.dispose immediately after the “using” block.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim cmd As New SqlCommand("select * from emp", getconnection)
Dim res As Integer = cmd.ExecuteNonQuery
getconnection.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Also we need to keep in mind when we get a connection after calling a function which return a connection object, we should always cache the connection object locally and close it. In the above code the getconnection.close is not closing the connection that is used to execute the query but creating another new connection and closing it.
Errors related to Connection leak
1) Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
2) SQL Server Does Not Exist Or Access Denied
3) General Network Error
Ways to confirm that its a connection leak
1> Client-Side Performance Counters
NET CLR Data
SqlClient: Current # connection pools
SqlClient: Current # pooled and nonpooled connections
SqlClient: Current # pooled connections
SqlClient: Peak # pooled connections
SqlClient: Total # failed commands
SqlClient: Total # failed connections
2> SQL Server Performance Counter
SQLServer:General Statistics
Logins/sec
Logouts/sec
User Connections
Figure 1
If we take a perfmon with the above counter, A lot of logins with a very few or no logouts indicates a connection leak. In my application I set the connection pool size to 5. since my application is leaking connection, you can see at one time its creating a lot of connections or doing a lot of log-ins and not closing any of them. Once the number of connections reached 5 we got the error. and we closed the application. You can see in the perfmon graph above that all the connection were closed when we closed the application. before that there were no log-outs.
3> Profiler
Similar to the perfmon result above , A lot of logins with no logouts indicates a connection leak. In the figure below the “ConnTest” is the name of my application. You need to specify the “Application Name =” in the connection string to get it reflected in the profiler. You will see a lot of logouts once you close the application.
Figure 2
4> Select * from sysprocesses
Run the command “Select * from sysprocesses” , If you see a lot of entries of you application in the result set its quite possible that your application is leaking connection.
Once you close your application , You won’t find any such entries in the result set.
Figure 3
If you are using Oracle as your database you can substitute this command with
SELECT LOGON_TIME, OSUSER, PROCESS, MACHINE, PROGRAM
FROM V$SESSION WHERE PROGRAM IS NOT NULL
5> Running out of port
If your application is leaking connection, It will keep on opening connection without closing them. For opening each connection it will use a port. at one time it might happen that the application box might run out of port. You can track that by the command “Netstat –aon” from the command prompt. If you see a lot of “Time_Wait” in the netstat output , this means that the client box is running out of port. The permanent solution for Connection Leak is always fixing the code. But If you really running out of ports a temporary fix would be to increase the no of ephemeral ports. Ephemeral ports are ports that are used for mainly temporary purpose for opening short lived connection. The no of ephemeral ports varies from 1024 to 65534 To increase the no of ephemeral ports
Ephemeral ports are ports that are used for mainly temporary purpose for opening short lived connection. we need to change the following registry entries
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
On the Edit menu, click New, and then add the following registry entry:
Value Name: MaxUserPort
Value Type: DWORD
Value data: 65534
Valid Range: 5000-65534 (decimal)
Default: 0x1388 (5000 decimal)
We need to increase the value to a higher one eg 65534.
Another fix for a lot of time_wait is adjusting the TcpTimedwaitDelay in the registry. This value indicates the amount of time a port will remain in Time_wait state if you do not close the connection. If you decrease this value, the port will be in time_wait state for less amount of time and will be reused sooner.
This key is located at -:
HKEY_LOCAL_MACHINE\System\CurrectControlSet\services\Tcpip\Parameters
The registry value is:
TcpTimedWaitDelay
Key: Tcpip\Parameters
Value Type: REG_DWORD - Time in seconds
Valid Range: 30-300 (decimal)
Default: 0xF0 (240 decimal)
We need to decrease this value to a lower one eg 30.
NOTE- PLEASE TAKE A COMPLETE BACKUP OF THE BOX BEFORE MAKING ANY CHANGES AND ALSO RESTART THE BOX AFTER CHANGING THE REGISTRY
For example if you run netstat -aon and you see that close to 4000 connections to the IP address of the target computer that is running SQL Server are in a TIME_WAIT state, you can both increase the default MaxUserPort setting and reduce the TcpTimedWaitDelay setting so that you do not run out of client anonymous ports. For example, you can set the MaxUserPort setting to 20000 and set the TcpTimedWaitDelay setting to 30. A lower TcpTimedWaitDelay setting means that the sockets wait in the TIME_WAIT state for less time. A higher MaxUserPort setting means that you can have more sockets in the TIME_WAIT state.
To know more about MaxUserPort and TcpTimedWaitDelay please refer to https://support.microsoft.com/kb/328476
This is quite typical in applications that leak connections. In some cases, this error only occurs when the customer has migrated a web application from the test environment to the production environment. This is typically because the test server has less memory and garbage collection is run often enough to clean up the leaked connections. In the production environment, the server typically has 2-4 times as much memory as in the test environment, thus there is little memory pressure on the machine to force a garbage collection.
Short time fix for connection leak
The permanent solution for connection leak is always to fix the code. But What if you discovered the connection pooling issue in production and you cannot take it offline to troubleshoot? Turn pooling off. Even though your app will take a performance hit, it shouldn't crash! Your memory footprint will also grow. What if it doesn't crash all that often, and you don't want to take a performance hit? Try this:
conn = new SqlConnection();
try
{
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_DB_NAME;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;"; // Notice Connection Timeout set to only two seconds!
conn.Open();
}
catch(Exception)
{
if (conn.State != ConnectionState.Closed) conn.Close();
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Pooling=false;Connect Timeout=45;";
conn.Open();
}
If I fail to open a pooled connection within two seconds, I am trying to open a non-pooled connection. This introduces a two second delay when no pooled connections are available, but if your connection leak doesn't show most of the time, this is a good steam valve.
To Know more about connection pooling please visit the following websites
https://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;191572
https://support.microsoft.com/kb/313480/en-us
https://msdn.microsoft.com/en-us/library/ms810829.aspx
https://msdn.microsoft.com/en-us/library/aa175863(SQL.80).aspx
https://msdn.microsoft.com/en-us/library/ms810810.aspx
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx
Author : Snehadeep , SQL Developer Engineer , Microsoft
Reviewed by : SMAT , SQL Escalation Services , Microsoft