Tenho quatro trabalhos do SQL Agent em execução contínua. Cada trabalho está executando um procedimento armazenado que consulta transações ATM recentes, tudo por meio do mesmo servidor vinculado. Isso acontece em um loop dentro de cada trabalho com um atraso de 5 segundos entre as execuções do proc (usando WAITFOR DELAY).
Especificamente, um dos quatro trabalhos está usando um procedimento armazenado e os outros três estão usando outro. Cada execução de trabalho tem seu próprio conjunto de parâmetros relacionados a padrões específicos de transações que acionarão uma resposta. Eles estão todos consultando o mesmo servidor vinculado.
Na maioria das vezes, tudo está funcionando bem. Mas ocasionalmente (quase *) qualquer um dos trabalhos falhará com este erro:
Provedor TCP: o nome de rede especificado não está mais disponível. [SQLSTATE 42000] (Erro 64) O provedor OLE DB "SQLNCLI11" para o servidor vinculado "ATMDB" retornou a mensagem "Falha no link de comunicação". [SQLSTATE 01000] (Erro 7412).
Isso só começou a acontecer depois que os trabalhos foram atualizados para serem executados continuamente com o loop WAITFOR, em vez de serem executados uma vez a cada minuto como uma execução de trabalho separada do SQL Agent. Essa alteração foi feita para evitar o registro excessivo de tarefas SQL e também para monitorar as transações do caixa eletrônico muito mais próximo do tempo real.
Não há consistência com a frequência com que eles falharão, mas é pelo menos algumas vezes ao dia.
*Eu digo "quase" acima, porque um dos trabalhos nunca falhou. Este é o que mais recebe "acertos" e, quando isso ocorre, a etapa do trabalho pode ser concluída, portanto, temos um registro explícito disso nos logs do trabalho SQL. (Em seguida, volta para a primeira etapa novamente após um atraso de 1 segundo.)
Todos os trabalhos são configurados para serem executados a cada minuto. Portanto, após uma falha, o trabalho será reiniciado no início do próximo minuto, portanto, isso não está afetando muito as coisas. É muito chato!
Suspeito que o SQL Native Client e como ele implementa o pool de conexões seja a raiz do problema. Durante o teste, atualizei o servidor vinculado em um dos dois procedimentos para ser diferente do outro. Dois nomes de host diferentes, mas ainda o mesmo servidor (só usei o arquivo hosts para criar um novo nome). Isso fazia com que um trabalho não falhasse mais, mas dois dos três outros trabalhos ainda falhavam intermitentemente. Depois de alternar o outro procedimento para também usar o mesmo servidor vinculado novamente, agora todos os trabalhos novamente falhariam ocasionalmente. Como os pools de conexão são específicos para a string de conexão, um nome de servidor diferente faria com que um pool diferente fosse usado.
Usando o monitor de recursos do Windows, posso ver que geralmente há três conexões TCP abertas para o servidor vinculado. Eles permanecem abertos nas mesmas portas por vários minutos, então acho que está usando algum tipo de pool de conexão. Quando o erro acontece, ele coincide com o fechamento de uma das conexões e logo em seguida uma nova conexão será aberta.
Minha teoria atual: como os processos estão em loop repetidamente durante o uso do servidor vinculado, algo no pool de conexões causa uma situação em que ele está tentando usar uma conexão que foi fechada por qualquer motivo. Talvez a única etapa do trabalho que "conclua" de vez em quando seja atualizada de alguma forma e evite que o erro ocorra nesse processo específico.
Tentei envolver as chamadas de procedimento armazenado em sp_executesql como uma solução alternativa para "forçar" outro processo ou contexto de execução, mas isso não ajudou.
Ambos os servidores são Windows 2012, rodando em VMWare.
Alguma idéia sobre como solucionar problemas adicionais?
O problema foi resolvido depois que os hosts da VM foram substituídos por um hardware mais recente. Isso sugere que foi um problema de nível de rede/hardware, e não algo devido à maneira específica como as consultas foram programadas ou como o SQL está configurado.