Recentemente, nosso painel de processos bloqueados tem relatado processos bloqueados na época em que fazemos nossa atualização estatística.
O motivo foi encontrado rapidamente: uma etapa de trabalho de estatísticas de atualização (T-SQL) que está iniciando na instância secundária e primária do SQL Server. O trabalho atualiza várias estatísticas no mesmo banco de dados, que faz parte de um Grupo de Disponibilidade AlwaysOn. Eu esperaria que isso falhasse na instância secundária.
Um resumo rápido do histórico de failover:
O Servidor A, que deve permanecer ativo devido ao licenciamento (será denominado Servidor Ativo), falhou inesperadamente no Servidor B (Servidor Passivo) no dia 20/02 às 21h.
Após o failover não planejado, fizemos outro failover manual (mas desta vez planejado) de volta ao Active Server em 21/02 às 12h.
Histórico de trabalho
Antes do primeiro failover, tudo estava bem, e o servidor ativo é o único que executa o trabalho.
Um trabalho em execução.
Vemos as atualizações de estatísticas sendo executadas no lado ativo. (que é réplica primária no momento)
Durante o curto período em que o servidor passivo foi a réplica primária, não tivemos nenhum monitoramento e o histórico do trabalho foi limpo.
Após o failover, de volta ao estado 'normal', após estar no primário no nó passivo por menos de 24 horas, a etapa de trabalho na instância passiva também foi iniciada e executada na instância ativa.
(eu matei as sessões).
Agora, a parte interessante para mim é que ambos os trabalhos estão sendo executados no servidor ativo, parecendo que o trabalho está usando o ouvinte para acessar o banco de dados. Mas pode ser um motivo totalmente diferente.
Há uma tarefa de cópia do PowerShell sendo executada todas as noites às 01:00 (dbatools):
powershell.exe Copy-DbaAgentJob -ExcludeJob "CopyJobs,CopyLogins" -Source INDCSPSQLA01 -Destination INDCSPSQLP01 -Force
Meu palpite agora é voltado para uma vez, a cópia do trabalho aconteceu a partir do nó secundário ativo --> nó passivo primário com -Force. Isso aconteceu em 21/02 01:00.
A questão
Por que a etapa de trabalho na instância passiva está sendo executada no banco de dados da instância ativa?
Lista de controle
Em ambas as instâncias, o destino do job é local:
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
O nome do servidor está correto
select name from sys.servers
select @@SERVERNAME
ambos retornam o servidor passivo.
Os IDs de trabalho de ativo e passivo são diferentes:
--08C63F07-0853-41DA-BC88-8FDF44AE491F -- passive
--E8C88965-C581-4E06-B651-CC10637FCEEF -- active
Ambos os trabalhos usam o banco de dados em questão em sua etapa:
@database_name=N'DB1',
--> Não deve ser acessível na instância passiva, resultando em falha. Sem secundários legíveis
O banco de dados não está acessível na instância passiva:
Versão de ambos os servidores: 14.0.3030.27
Exemplo de comando da etapa T-SQL Job
@subsystem=N'TSQL',
@command=N'update statistics dbo.table with fullscan ...'
Nada está sendo executado na instância passiva quando o trabalho é iniciado.
EDITAR:
Reiniciar o agente no nó passivo 'corrige' isso, resultando em um novo erro na execução:
Não é possível conectar ao SQL Server 'INDCSPSQLP01'. A etapa falhou.
Como resultado, ele não atualiza mais as estatísticas na instância primária
Hostnames = Servidor passivo e ativo, trabalhos em execução visivelmente.
Informações do aplicativo
SQLAgent - TSQL JobStep (Job 0x9D358B2EF6C53C4BAD6A61CA87D51BF5 : Step 1)
SQLAgent - TSQL JobStep (Job 0x6589C8E881C5064EB651CC10637FCEEF : Step 1)
Não tenho um diagnóstico de por que esse problema ocorreu, mas se você tiver trabalhos em execução em bancos de dados que estão em grupos de disponibilidade, é melhor incluir uma verificação na etapa 1 que usa a
fn_hadr_is_primary_replicafunção para verificar se está sendo executado no primário ou secundário.Configure esta etapa para encerrar o trabalho em caso de falha. Isso é melhor do que tentar executar algo que falha porque está atingindo um secundário.