Asp.net WebForms. Timeout error exceeded in SQL Server. How to solve?

4

In an ASP.NET WebForms application that uses SQL Server 2008 a search field that works with AJAX requests method asmx .

To avoid mass requests I added a delay method in the keyup method 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 an example web:

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

The application itself usually works with multiple users accessing at the same time, however, in my screen tests (debug mode), when realizing that one query was triggered while another was still being done, 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: Timeout expired. The timeout period was reached before a pool connection was obtained. This could have happened 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 Dreamspark , not > Express . Already where the application is installed is the Express version.

The requests to webMethod work, but only when requests are triggered simultaneously, as I said, is that the error occurs.

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 in this project are inherited from a CustomDAO class:

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

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

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

What could be happening?

    
asked by anonymous 20.08.2014 / 22:19

2 answers

5

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 connections are not closed early and that you can open at least 5 concurrent connections.

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

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

HttpServerUtility.ScriptTimeout = TimeSpan.FromMinutes(60).Seconds; 
    
20.08.2014 / 22:56
2

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

In debug mode mode I put a break-point in methods Dispose
21.08.2014 / 19:52