C# 如何解决最大连接池错误

How to Solve Max Connection Pool Error

提问by Harshit Tailor

I have a application in asp.net 3.5 and Database is Sql server 2005.

我在 asp.net 3.5 中有一个应用程序，数据库是 Sql server 2005。

"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."

Some time this error occured How to solve this error..

有一段时间出现此错误 如何解决此错误..

I try SqlConnection.ClearAllPools();but this also not working.

我试过了，SqlConnection.ClearAllPools();但这也不起作用。

SqlCommand cmdToExecute = new SqlCommand();
            cmdToExecute.CommandText = "dbo.[sp_user_listing]";
            cmdToExecute.CommandType = CommandType.StoredProcedure;
            DataTable toReturn = new DataTable("courier_user_listing");
            SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);

            // Use base class' connection object
            cmdToExecute.Connection = sqMainConnection;

            try
            {
                cmdToExecute.Parameters.Add(new SqlParameter("@suser_name", SqlDbType.VarChar, 250, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _user_name));


                if (blnMainConnectionIsCreatedLocal)
                {
                    // Open connection.
                    sqMainConnection.Open();
                }
                else
                {
                    if (CPMainConnectionProvider.IsTransactionPending)
                    {
                        cmdToExecute.Transaction = CPMainConnectionProvider.CurrentTransaction;
                    }
                }

                // Execute query.
                adapter.Fill(toReturn);
                i32ErrorCode = (Int32)cmdToExecute.Parameters["@iErrorCode"].Value;

                if (i32ErrorCode != (int)LLBLError.AllOk)
                {
                    // Throw error.
                    throw new Exception("Stored Procedure 'sp_courier_user_SelectAll' reported the ErrorCode: " + i32ErrorCode);
                }

                return toReturn;
            }
            catch (Exception ex)
            {
                // some error occured. Bubble it to caller and encapsulate Exception object
                throw new Exception("Courier_user::SelectAll::Error occured.", ex);
            }
            finally
            {
                if (blnMainConnectionIsCreatedLocal)
                {
                    // Close connection.
                    sqMainConnection.Close();
                }
                cmdToExecute.Dispose();
                adapter.Dispose();
            }

采纳答案by Elger Mensonides

Check against any long running queries in your database.

检查数据库中任何长时间运行的查询。

Increasing your pool size will only make your webapp live a little longer (and probably get a lot slower)

增加池大小只会使您的 web 应用程序存活时间更长（并且可能会变慢很多）

You can use sql server profiler and filter on duration / reads to see which querys need optimization.

您可以使用 sql server profiler 并过滤持续时间/读取来查看哪些查询需要优化。

I also see you're probably keeping a global connection?

我也看到你可能保持全球联系？

blnMainConnectionIsCreatedLocal

Let .net do the pooling for you and open / close your connection with a using statement.

让 .net 为您进行池化并使用 using 语句打开/关闭您的连接。

Suggestions:

建议：

1. Always open and closea connection like this, so .net can manage your connections and you won't run out of connections:

       using (SqlConnection conn = new SqlConnection(connectionString))
       {
        conn.Open();
        // do some stuff
       } //conn disposed
   

2. As I mentioned, check your query with sql server profilerand see if you can optimize it. Having a slow query with many requests in a web app can give these timeouts too.

1. 始终打开和关闭这样的连接，因此 .net 可以管理您的连接，并且您不会用完连接：

       using (SqlConnection conn = new SqlConnection(connectionString))
       {
        conn.Open();
        // do some stuff
       } //conn disposed
   

2. 正如我所提到的，请使用sql server profiler检查您的查询，看看您是否可以对其进行优化。在 Web 应用程序中对许多请求进行缓慢查询也会导致这些超时。

回答by Manoj Purohit

May be this is alltime multiple connection open issue, you are somewhere in your code opening connections and not closing them properly. use

可能这是一直以来的多连接打开问题，您在代码中的某处打开连接但没有正确关闭它们。用

 using (SqlConnection con = new SqlConnection(connectionString))
        {
            con.Open();
         }

Refer this article: http://msdn.microsoft.com/en-us/library/ms254507(v=vs.80).aspx, The Using block in Visual Basic or C# automatically disposes of the connection when the code exits the block, even in the case of an unhandled exception.

请参阅这篇文章：http: //msdn.microsoft.com/en-us/library/ms254507(v=vs.80).aspx，Visual Basic 或 C# 中的 Using 块会在代码退出块时自动处理连接，即使在未处理的异常的情况下。

回答by Chamu

Before you begin to curse your application you need to check this:

在你开始诅咒你的应用程序之前，你需要检查一下：

1. Is your application the only one using that instance of SQL Server. a. If the answer to that is NO then you need to investigate how the other applications are consuming resources on your SQl Server.run b. If the answer is yes then you must investigate your application.

2. Run SQL Server Profiler and check what activity is happening in other applications (1a) using SQL Server and check your application as well (1b).

3. If indeed your application is starved off of resources then you need to make farther investigations. For more read on this http://sqlserverplanet.com/troubleshooting/sql-server-slowness

1. 您的应用程序是唯一使用该 SQL Server 实例的应用程序吗？一种。如果答案是否定的，那么您需要调查其他应用程序如何消耗您的 SQL Server.run b 上的资源。如果答案是肯定的，那么您必须调查您的申请。

2. 运行 SQL Server Profiler 并使用 SQL Server 检查其他应用程序 (1a) 中发生的活动，并检查您的应用程序 (1b)。

3. 如果您的应用程序确实缺乏资源，那么您需要进行进一步调查。有关此http://sqlserverplanet.com/troubleshooting/sql-server-slowness 的更多信息

回答by Chamu

Here's what u can also try....

这是你也可以尝试的......

run your application....while it is still running launch your command prompt

运行您的应用程序....在它仍在运行时启动您的命令提示符

while your application is running type netstat -non the command prompt. You should see a list of TCP/IP connections. Check if your list is not very long. Ideally you should have less than 5 connections in the list. Check the status of the connections.
If you have too many connections with a TIME_WAIT status it means the connection has been closed and is waiting for the OS to release the resources. If you are running on Windows, the default ephemeral port rang is between 1024 and 5000 and the default time it takes Windows to release the resource from TIME_WAIT status is 4 minutes. So if your application used more then 3976 connections in less then 4 minutes, you will get the exception you got.

当您的应用程序运行时，在命令提示符下键入netstat -n。您应该会看到 TCP/IP 连接列表。检查您的列表是否不是很长。理想情况下，列表中的连接数应少于 5 个。检查连接状态。
如果您有太多处于 TIME_WAIT 状态的连接，则表示连接已关闭并正在等待操作系统释放资源。如果您在 Windows 上运行，则默认临时端口范围介于 1024 和 5000 之间，Windows 从 TIME_WAIT 状态释放资源所需的默认时间为 4 分钟。因此，如果您的应用程序在不到 4 分钟的时间内使用了 3976 个以上的连接，您将收到异常。

Suggestions to fix it:

修复建议：

1. Add a connection pool to your connection string.

1. 将连接池添加到您的连接字符串。

If you continue to receive the same error message (which is highly unlikely) you can then try the following: (Please don't do it if you are not familiar with the Windows registry)

如果您继续收到相同的错误消息（这极不可能），您可以尝试以下操作：（如果您不熟悉 Windows 注册表，请不要这样做）

1. Run regeditfrom your run command. In the registry editor look for this registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters:

1. 从您的运行命令运行regedit。在注册表编辑器中查找此注册表项：HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters：

Modify the settings so they read:

修改设置，使其显示为：

MaxUserPort = dword:00004e20 (10,000 decimal) TcpTimedWaitDelay = dword:0000001e (30 decimal)

MaxUserPort = dword:00004e20 (10,000 十进制) TcpTimedWaitDelay = dword:0000001e (30 十进制)

This will increase the number of ports to 10,000 and reduce the time to release freed tcp/ip connections.

这会将端口数增加到 10,000，并减少释放释放的 tcp/ip 连接的时间。

Only use suggestion 2 if 1 fails.

如果 1 失败，则仅使用建议 2。

Thank you.

谢谢你。