Timeout SqlConnection.Open() ADO.NET

[McGMS]

Estou trabalhando em um projeto de criações de bots para automação de tarefas e estou enfrentando alguns problemas relacionados a timeout de conexao.

O banco de dados e SQL Server, toda a manipulação do banco está sendo feita a partir de procedures executadas por SQL Command, estou usando Using nas minhas conexoes e mesmo assim está dando timeout de conexao

Quando eu executo algumas vezes funciona porém lá na centésima vez ele estoura esta exception.

Abaixo está o método que eu estou executando;

//ADICIONA TASK
    public void AddTask(string task, string owner, string args)
    {
        LogIntegration.AddLog("Iniciando inserção de tarefa");
        //PROC DE EXECUCAO
        string proc = "PROC_INSERT_TASK_TASK";

        //CRIANDO CONEXAO

        LogIntegration.AddLog(args);

        try
        {

            using (SqlConnection conexao = SQLConnectionFactory.getConnection())
            {
                //ABRINDO CONEXAO
                conexao.Open();

                //CRIANDO COMANDO
                SqlCommand comando = new SqlCommand(proc, conexao);

                //SETANDO COMMANDO TYPE
                comando.CommandType = System.Data.CommandType.StoredProcedure;

                //SETANDO PARAMETROS
                comando.Parameters.AddWithValue("@TATY_TX_NAME", task);
                comando.Parameters.AddWithValue("@USER_TX_EMAIL", owner);
                comando.Parameters.AddWithValue("@TASK_TX_ARGS", args);

                comando.CommandTimeout = 120;

                //EXECUTANDO COMANDO
                comando.ExecuteNonQuery();

                LogIntegration.AddLog("Inserida com sucesso!");

            }

        }
        
        catch (SqlException e)
        {
            LogIntegration.AddLog("Erro ao inserir tarefa : "+e.Message);
        }


    }

ERRO MESAGE:

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

STACKTRACE:
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at RecepcaoBOT.DAO.RecepcaoDAO.AddTask(String task, String owner, String args) in R:\Informatica\Guilherme Maciel\PROJETOS\ETL BOT\CODIGO FONTE\ETL2\RecepcaoBOT\DAO\RecepcaoDAO.cs:line 136 at RecepcaoBOT.Negocio.Importador.OperatorFileImport.LoteImport(FileAttachment arquivo, ComandoEmail comando, EmailMessage email) in R:\Informatica\Guilherme Maciel\PROJETOS\ETL BOT\CODIGO FONTE\ETL2\RecepcaoBOT\Negocio\Importador\OperatorFileImport.cs:line 171 at RecepcaoBOT.Negocio.Importador.OperatorFileImport.BasicFilterImport(Int32 tarefa, ComandoEmail comando, EmailMessage email) in R:\Informatica\Guilherme Maciel\PROJETOS\ETL BOT\CODIGO FONTE\ETL2\RecepcaoBOT\Negocio\Importador\OperatorFileImport.cs:line 49 at RecepcaoBOT.Negocio.Importador.Importar.ImportarArquivo(Int32 tarefa, ComandoEmail comando, EmailMessage email) in R:\Informatica\Guilherme Maciel\PROJETOS\ETL BOT\CODIGO FONTE\ETL2\RecepcaoBOT\Negocio\Importador\Importar.cs:line 41 at RecepcaoBOT.Negocio.Atribuidor.AtribuirEmail(Int32 tarefa, ComandoEmail comando, EmailMessage email, String bodyComp) in R:\Informatica\Guilherme Maciel\PROJETOS\ETL BOT\CODIGO FONTE\ETL2\RecepcaoBOT\Negocio\Atribuidor.cs:line 70 at RecepcaoBOT.CORE.RecepcaoBot.Main(String[] args) in R:\Informatica\Guilherme Maciel\PROJETOS\ETL BOT\CODIGO FONTE\ETL2\RecepcaoBOT\CORE\RecepcaoBot.cs:line 79

[Dragoon]

comando.CommandTimeout = 120;

retire essa linha:

comando.CommandTimeout = 120;

faça o teste! e depois nos avise

[McGMS]

Eu tinha colocado esta linha para vez se resolvia o problema, mas por desencargo testei novamente e o erro persistiu. =(

[Dragoon]

Eu tinha colocado esta linha para vez se resolvia o problema, mas por desencargo testei novamente e o erro persistiu. =(

É o seguinte o erro vem antes, deve ser isso, tipo como você está chamando antes tudo isso.!?

[McGMS]

Tenho uma classe chamada SQLConnectionFactory que pega a connectionString e cria uma SqlConnection fechada, o bug está dando quando eu chamo o .Open(), ele trava e lança excepion. Não sei se entendi muito bem o que tu quis dizer rs

[Dragoon]

Tenho uma classe chamada SQLConnectionFactory que pega a connectionString e cria uma SqlConnection fechada, o bug está dando quando eu chamo o .Open(), ele trava e lança excepion. Não sei se entendi muito bem o que tu quis dizer rs

Porque você está chamando muitas vezes!

Cole a classe aqui?

[McGMS]

Segue abaixo a classe:

internal class SQLConnectionFactory
{   

    //RETORNA CONEXAO COM O BANCO
    public static SqlConnection getConnection()
    {
        return new SqlConnection(RecepcaoConfig.getInstance().Strcnn_lockton_inp);

    }

}

[Dragoon]

Faz o seguinte!

Feche as conexões depois da inserção.

public void AddTask(string task, string owner, string args)
{
	LogIntegration.AddLog("Iniciando inserção de tarefa");		
	LogIntegration.AddLog(args);
	try
	{

		using (SqlConnection conexao = SQLConnectionFactory.getConnection())
		using (SqlCommand comando = new SqlCommand("PROC_INSERT_TASK_TASK", conexao))
		{
			
			if (conexao.State == ConnectionState.Closed) 
			{
				conexao.Open();
			}
			
			comando.CommandType = System.Data.CommandType.StoredProcedure;			
			comando.Parameters.AddWithValue("@TATY_TX_NAME", task);
			comando.Parameters.AddWithValue("@USER_TX_EMAIL", owner);
			comando.Parameters.AddWithValue("@TASK_TX_ARGS", args);
			
			if (comando.ExecuteNonQuery() > 0)
			{
				LogIntegration.AddLog("Inserida com sucesso!");
			}
			
			commando.Dispose();
			conexao.Close();
			
		}

	}	
	catch (SqlException e)
	{
		LogIntegration.AddLog("Erro ao inserir tarefa : "+e.Message);
	}
}

Se não der certo talvez trabalhar com Singleton (talvez), mas, não acho necessário. Isso também depende da frequência de chamada!

[McGMS]

Obrigado pela ajuda

Testei o seu code e mesmo assim não obtive exito, anteriormente eu fiz um teste com singleton utilizando uma única conexao e o erro persistiu, estou começando a pensar que é alguma configuração no Sql Server que está dando este erro, pois o mesmo ocorre no .Open() e também após um tempo executando diversos comandos em uma única conexao aberta.

[Dragoon]

Testei o seu code e mesmo assim não obtive exito, anteriormente eu fiz um teste com singleton utilizando uma única conexao e o erro persistiu, estou começando a pensar que é alguma configuração no Sql Server que está dando este erro, pois o mesmo ocorre no .Open() e também após um tempo executando diversos comandos em uma única conexao aberta.

• Como você chama esse método?
• Qual a frequência?

O problema vem antes! passa o código do que está realmente fazendo, porque, não é normal tal erro!

[McGMS]

Este método é chamado diversas vezes em uma iteração, o teste que eu estou fazendo é com aproximadamente 1500 iterações continuas.

Está sendo chamado em um “for”

[Dragoon]

Este método é chamado diversas vezes em uma iteração, o teste que eu estou fazendo é com aproximadamente 1500 iterações continuas.

Está sendo chamado em um “for”

Ai é o problema! não é no código, mas, a conexão com o banco não é fechada de imediato, e nem o objeto é é removido automáticamente com GC, isso tem um tempo para acontecer!

Como é chamado simultaneamente acontece esses problemas. Tem que fazer assim no método que você chama Add passar uma conexão com o banco e no final da iteração fechar no essa conexão.! Ou seja a conexão ficar aberta até ele acabar o for!

Vamos fazer um teste:

mude seu método assim:

public void AddTask(SqlConnection conexao, string task, string owner, string args)
{
	LogIntegration.AddLog("Iniciando inserção de tarefa");		
	LogIntegration.AddLog(args);
	try
	{
		
		using (SqlCommand comando = new SqlCommand("PROC_INSERT_TASK_TASK", conexao))
		{
			
			if (conexao.State == ConnectionState.Closed) 
			{
				conexao.Open();
			}
			
			comando.CommandType = System.Data.CommandType.StoredProcedure;			
			comando.Parameters.AddWithValue("@TATY_TX_NAME", task);
			comando.Parameters.AddWithValue("@USER_TX_EMAIL", owner);
			comando.Parameters.AddWithValue("@TASK_TX_ARGS", args);
			
			if (comando.ExecuteNonQuery() > 0)
			{
				LogIntegration.AddLog("Inserida com sucesso!");
			}   			
				
		}

	}	
	catch (SqlException e)
	{
		LogIntegration.AddLog("Erro ao inserir tarefa : "+e.Message);
	}
}

e no for:

using (SqlConnection conexao = SQLConnectionFactory.getConnection())
{
    for(int i = 0; i < 1500; i++)
    {
        AddTask(conexao, task, owner, args);
    }
}

Tipo mais ou menos assim, porque ai mesmo a conexão aberta com 1500 iterações ele vai utilizar 1 processo e resolver todas as SQL! Teste!?

[marco_aurelioo]

Acredito que você esta estourando o numero de conexões com o banco então faz o seguinte: inclui no método um campo finnaly:

try {
	
}catch(Exception e) {
	
}finally {
	conexao.close()
}

[Dragoon]

try {

}catch(Exception e) {

}finally {
conexao.close()
}

@marco_aurelioo mas, isso só vai lançar uma exceção, não vai resolver o problema.

O problema é que ele abri muitas conexões com o banco, aonde o mesmo só precisava de um conexão ativa, e foi a minha proposta na resposta!

Só coloquei isso como forma de ilustrar e ajudar a entender o problema ocasionado por multiplas conexões abertas no Servidor de Banco de Dados.

[marco_aurelioo]

mas, isso só vai lançar uma exceção, não vai resolver o problema.

não vai lançar uma exceção vai garantir que quando ele termine de usar a conexão ele faça o serviço todo inclusive devolvendo o recurso de conexão.
isso sempre deve ser utilizado quando vc utiliza recursos do tipo leitura de arquivo, download de arquivos e conexões de banco.

[Dragoon]

não vai lançar uma exceção vai garantir que quando ele termine de usar a conexão ele faça o serviço todo inclusive devolvendo o recurso de conexão.

isso sempre deve ser utilizado quando vc utiliza recursos do tipo leitura de arquivo, download de arquivos e conexões de banco.

vai lançar uma exceção porque primeiro ele passa pelo Erro! porque o Erro vai existir. Se o erro Existir onde ele passa primeiro?

Preste atenção o código tem que finalizar naturalmente e o que você propôs ainda ele vai passar na parte da exceção.

Ou seja, só um estrutura de erro não resolve o problema dele!

Veja a minha resposta, na verdade veja o debate por completo até chegar num consenso