如何解决最大连接池时出错 [英] How to Solve Max Connection Pool Error

问题描述

我在asp.net 3.5和数据库应用是SQL Server 2005中。

 超时过期。超时时间已过之前从池中获取连接。出现这种情况可能是因为所有池连接均在使用，达到最大池大小。
 

有的时候这个错误发生如何解决这个错误。

我尝试 SqlConnection.ClearAllPools（）; 但是这也不能正常工作

 的SqlCommand cmdToExecute =新的SqlCommand（）;
            cmdToExecute.CommandText =DBO [sp_user_listing];
            cmdToExecute.CommandType = CommandType.StoredProcedure;
            数据表toReturn =新的DataTable（courier_user_listing）;
            SqlDataAdapter的适配器=新SqlDataAdapter的（cmdToExecute）;            //使用基类的连接对象
            cmdToExecute.Connection = sqMainConnection;            尝试
            {
                cmdToExecute.Parameters.Add（新的SqlParameter（@ SUSER_NAME，SqlDbType.VarChar，250，ParameterDirection.Input，真，0，0，，DataRowVersion.Proposed，_USER_NAME））;
                如果（blnMainConnectionIsCreatedLocal）
                {
                    //打开连接。
                    sqMainConnection.Open（）;
                }
                其他
                {
                    如果（CPMainConnectionProvider.IsTransactionPending）
                    {
                        cmdToExecute.Transaction = CPMainConnectionProvider.CurrentTransaction;
                    }
                }                //执行查询。
                adapter.Fill（toReturn）;
                i32Error code =（Int32）已cmdToExecute.Parameters [@ IERROR code]值。                如果（i32Error code！=（INT）LLBLError.AllOk）
                {
                    //引发错误。
                    抛出新的异常（存储过程sp_courier_user_SelectAll'报告错误code：+ i32Error code）;
                }                返回toReturn;
            }
            赶上（异常前）
            {
                //一些错误发生。泡泡它调用者和封装异常对象
                抛出新的异常（Courier_user ::全选::发生错误。恩）;
            }
            最后
            {
                如果（blnMainConnectionIsCreatedLocal）
                {
                    //关闭连接。
                    sqMainConnection.Close（）;
                }
                cmdToExecute.Dispose（）;
                adapter.Dispose（）;
            }
 

解决方案

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

增加您池的大小，只会让你的web应用活得更长一点（也可能是得到了很多慢）

您可以使用SQL Server Profiler和过滤器上的时间/读取，看看哪些querys需要优化。

我也看到你很可能保持全局连接？

  blnMainConnectionIsCreatedLocal
 

让.NET为你打开/关闭你用using语句连接做池。

建议：

1. 始终开放和关闭这样的连接，因此.NET可以管理你的连接，你就不会跑出来的连接：

    使用（SqlConnection的康恩=新的SqlConnection（的connectionString））
       {
        conn.Open（）;
        //做一些东西
       } //处理康涅狄格州
    




2. 正如我所说，是 SQL Server设置检查您的查询，看看是否可以优化它。其在Web应用程序可以给这些超时太多的请求的慢速查询。

I have a application in asp.net 3.5 and Database is 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.

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();
            }

解决方案

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)

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

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.

Suggestions:

1. Always open and close a 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 profiler and see if you can optimize it. Having a slow query with many requests in a web app can give these timeouts too.

这篇关于如何解决最大连接池时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！