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.

遇到这种错误信息, 一般是自己忘记了关闭SqlConnection所致, 也叫SQL Connection Leak.

 

下面的代码说明了这个问题.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace ConnPoolErrorDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string connString = @"Data Source=TestDataSource;
                    Initial Catalog=Northwind;
                    Integrated Security=True;
                    Max Pool Size=20;
                    Connection Timeout=10";

            try
            {
                for (int i = 0; i < 50; i++)
                {
                    // Create connection, command and open the connection
                    SqlConnection sc = new SqlConnection(connString);
                    SqlCommand sCmd = new SqlCommand("SELECT * FROM Shippers", sc);

                    sc.Open();

                    // Print info
                    Console.WriteLine("Connections open: {0}", i.ToString());
                    // This will cause the error to show.
                    SqlDataReader sdr = sCmd.ExecuteReader();
                    sdr.Close();

                    // -- Replacing the two rows above with these will remove the error --
                    //SqlDataReader sdr = sCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    //sdr.Close();

                    // -- or --
                    // Explicity close the connection
                    // sc.Close();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }
    }
}

 

在关闭SQL Connection之前从函数中退出(return), 或者程序出现了exception, 而SQL Connection还没有关闭, 都可以导致这个错误的发生.

 

如何监控SQL Connection Leak?

=================

1. 使用netstat -abno>c:\temp\ports.txt命令, 检查类似下面的连接.

Proto  Local Address          Foreign Address        State                    PID

TCP   192.168.1.2:1433     192.168.1.5:3018      ESTABLISHED     1980
[sqlservr.exe]

2. 创建一个ASPNET页面, 在这个页面中调用GC.Collect两次. 如果SQL连接的数量在访问了这个页面之后急剧下降, 基本可以确定有SQL Connection Leak发生了.

3. 使用SQL Server Performance Counter也可以确定是否存在sql 连接的泄露. 观察下面的三个counters:

        • Logins/sec

        • Logouts/sec

        • User Connections

4. 使用SQL Profiler, 只抓取Audit login 和Audit logout两个事件. 在SQL Profiler trace中, 你可以看到具体的应用程序的名字.

 

如何避免SQL Connection Leak?

==================

1. 显式地关闭所有的Connection对象.

2. 显式地关闭所有的DataReader对象. 添加CommandBehavior.CloseConnection选项, 上面的代码给出了例子.

3. 如果你使用DataAdapter, 你必须显式地关闭connection.

4. 尽可能地你的Connection的范围在local, 越清楚越好. 最好像这样:

using (SqlConnection connection = new SqlConnection(connectionString))
{
          SqlCommand command = new SqlCommand(queryString, connection);
          command.Connection.Open();
          command.ExecuteNonQuery();
}

5. 在return之前, 一定要close connection.

 

参考资料:

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.

http://bytes.com/topic/asp-net/answers/311646-timeout-expired-timeout-period-elapsed-prior-obtaining-connection-pool-may-have-occurred-because-all-pooled-connections-were-use-max-pool-size-reached