Solucionando Problemas: "No transaction is active" no SQL SERVER (pt-BR)

Problema

Ao tentar executar uma transação distribuída você pode receber o erro abaixo e ao executar novamente o mesmo script é executado com sucesso.

OLE DB provider "SQLNCLI10" for linked server "<LinkedServer>" returned message "No transaction is active.".

Msg 7391, Level 16, State 2, Line 4
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "<LinkedServer>" was unable to begin a distributed transaction.

Este erro pode ocorrer em ambientes com servidores geograficamente distribuídos, pois por default o MSDTC aguarda até 4 segundos na tentativa de alistar-se no DTC do servidor destino, caso o tempo seja maior do que os 4 segundos, o DTC retorna a mensagem de erro “No transaction is active” . A latência do link geralmente é um dos grandes causadores deste problema.

Como identificar:

Primeiro precisamos verificar se existe uma comunicação entre o DTC nos dois servidores. Para isto execute o comando abaixo no Prompt de Comando do servidor de destino da transação.

NETSTAT – anbo >c:\netstat.log

Abra o arquivo c:\netstat.log e procure pelo IP do seu servidor de origempara o serviço MSDTC

Nota: Se você fez uma tentativa há menos de 10 minutos, você encontrará o registro para o MSTDC, caso contrário você não o encontrará.

O Windows mantém esta conexão por 10 minutos caso não tenha atividades neste protocolo, caso contrário ele é encerrado, e é exatamente ai que esta o problema, ao tentar restabelecer a conexão e o DTC demora mais de 4 segundos o erro é exibido.

Você pode usar o WinRM que é uma ferramenta da Microsoft que inicia uma transação distribuída ou simplesmente usar um script de sua aplicação, por ex.

set nocount on
select getdate()
BEGIN DISTRIBUTED TRANSACTION
SELECT top 1 * FROM [<LinkedServer>].[msdb].dbo.restorehistory
SELECT top 1 * FROM [master].dbo.syslogins
COMMIT TRANSACTION

No WinRM você precisa iniciar o arquivo WinRm.exe no servidor destino, no servidor de origem onde a transação é iniciada executar no Prompt de comando RMclient.exe /s <Servidor SQL Server destino>

Se você esta a mais de 10 min sem executar uma tentativa de transação e não tem o registro para o IP da maquina de origem para o serviço do MSDTC na máquina do destino (netstat –anbo) você deverá receber o erro acima.

Após executar o testes acima execute novamente o comando NETSTAT –anbo >c:\netstat.log no servidor de destino.
Novamente, verifique se existe o registro para o IP do servidor origem para o serviço MSDTC, você deverá encontrar o registro mesmo recebendo o erro.

Analisando o trace do MSDTC

Para analisar o trace do MSDTC, precisamos gerar o arquivo, para isto basta seguir as etapas abaixo:

No servidor de origem da transação, em Component Services acesse Computers\My Computer\Distributed Transaction Coordinator\Local DTC clique com o botão direito e clique em properties

Clique em:

1º Flush Data
2º Stop Session
3º New Session

Verifique no Windows Explorer o nome do arquivo mais recente no diretório C:\Windows\System32\MSDTC\Trace

Acesse o prompt de comando do diretório do trace e digite o comando abaixo:

msdtcvtr -tracelog <nome do arquivo de trace> -o c:\traceDTC

No diretório C:\ será gerado um arquivo traceDTC.CSV, ao editar este arquivo veremos mais alguns detalhes sobre o erro

pid=15364 ;tid=18312 ;time=08/25/2011-07:22:13.833 ;seq=3559667 ;eventid=TRANSACTION_PROPOGATION_FAILED_CONNECTION_DOWN_FROM_REMOTE_TM ;tx_guid=d5113606-db6d-4522-9846-2579bf69ed64 ;"TM Identifier='MSDTC$083767b6-f12c-404d-9c4a-3c5944d8276c '" ;"failed to propogate transaction to child node '<servidor>' because the connection with the remote transaction manager went down"

pid=15364 ;tid=18312 ;time=08/25/2011-07:22:13.833 ;seq=3559668 ;eventid=TRANSACTION_ABORTING ;tx_guid=d5113606-db6d-4522-9846-2579bf69ed64 ;"TM Identifier='MSDTC$083767b6-f12c-404d-9c4a-3c5944d8276c '" ;"transaction is aborting"

Solucionando o problema:

Para resolver este problema, podemos aumentar este timeout alterando uma chave de registry conforme o KB922430

Para servidores Stand Alone

Na pasta HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC criar a chave DWORD CmMaxNumberBindRetries com valor decimal (60)

Para servidores Clusterizados

Na pasta HKEY_LOCAL_MACHINE\Cluster\Resources\<chave onde esta o recurso do msdtc>\MSDTCPRIVATE\MSDTC criar a chave DWORD CmMaxNumberBindRetries com valor decimal (60)

Após a inclusão da chave de registro, deve-se reiniciar o serviço do MSDTC

Nota: O valor informado nesta chave equivale a metade em segundo, para 60 = 30 segs.