Asp.net Webforms. Timeout error exceeded in SQL Server. How to resolve?

Question

In an application ASP.NET Webforms who uses the SQL Server 2008 I am creating a screen that has a search field that works with requests AJAX a method of a Web Service asmx.

To avoid bulk requests I added a delay in the method keyup of input:

$("#txtPesquisa").keyup(function () {
    delay(function () {
        directoryTreeRemake();
    }, 1000);
});

function directoryTreeRemake() {
    $("#directoryTree").fancytree("destroy");
    $("#directoryTree").fancytree({
        source: $.ajax({
            url: "/Servicos/PublicacaoServico.asmx/ObterDiretorios",
            data: {
                ...
            }
        }),
    });
}

I copied this delay function from a web example:

var delay = (function () {
    var timer = 0;
    return function (callback, ms) {
        clearTimeout(timer);
        timer = setTimeout(callback, ms);
    };
})();

Then, after each (1) second of the event keyup a request is made to the webMethod.

The application itself works normally with multiple users accessing at the same time, however, in my tests from this screen (mode debug), when realizing that a query was triggered while another one was being made yet, a database access error is generated:

An Exception of type 'System.Invalidoperationexception' occurred in System.Data.dll but was not handled in user code

Additional information: Time limit expired. The time limit was reached before a pool connection was obtained. This may have been because all pool connections were in use and the maximum pool size was reached.

The error happens exactly in the command Open():

public void Open()
{
    if (connection.State == ConnectionState.Closed)
        connection.Open();  // <-- onde o erro ocorre
}

On my computer I have a version of SQL Server obtained by Dreamspark, is not the Express. Where the application is installed is the version Express.

webMethod requests work, but only when the requests are fired simultaneously, as I said, does the error occur.

My Connection String:

 Data Source=(local); User Id=Usuario; Password=******;
 Initial Catalog=CRM; MultipleActiveResultSets=True

Connection class with the main methods used to access the bank:

public class DbConnection : IDisposable
{
    private SqlConnection connection = null;

    public DbConnection() {
        connection = new SqlConnection(ConfigurationManager
            .ConnectionStrings["DefaultConnection"].ConnectionString);
    }

    public void Dispose() {
        if (connection != null && connection.State == ConnectionState.Open)
            connection.Close();
        if (connection != null)
            connection.Dispose();
        connection = null;
    }

    public void Open() {
        if (connection.State == ConnectionState.Closed)
            connection.Open();
    }

    public void Close() {
        if (connection.State == ConnectionState.Open)
            connection.Close();
    }
    ....
}

All data access classes of this project are inherited from a class CustomDAO:

public class CustomDAO : IDisposable
{
    protected DbConnection dbConnection = null;

    public CustomDataAccess() {
        dbConnection = new DbConnection();
    }

    public void Dispose() {
        if (dbConnection != null)
            dbConnection.Dispose();
    }
}

What might be going on?

Answer 1

Possibly the Pool is too short. Add the following to your Connection String:

Min Pool Size=5;Max Pool Size=250; Connect Timeout=3

This ensures that the executed connections will not be closed before time and that you can open at least 5 simultaneous connections.

Alternatively, you can also make the change by code, increasing the CommandTimeout of your SqlCommand for more than 30 seconds.

There is also the option to set more time for the execution of your request, which in Web Forms is around 90 seconds, redefining the property ScriptTimeout class HttpServerUtility:

HttpServerUtility.ScriptTimeout = TimeSpan.FromMinutes(60).Seconds; 

Answer 2

In fact the problem was the amount of connections with the SQL Server that were not being closed.

On-mode debug I put a break-point in the methods Dispose of the classes DbConnection and CustomDAO, and with that I verified that no stops were made in these methods, where the connection should be closed.

The classes then were not being released from memory for some reason, and so the connections were not being closed.

I decided to close the connections manually at the end of each method of the classes of the data access layer. With that the problems did not happen again.

The only thing that puzzles me is that it happened now, when I created this call to webMethod, and not before while the application was already in use.