PROBLEMA: The PowerShell subsystem failed to load \ SUSPENDED JOB \ MSDB - SYSSUBSYSTEMS


Olá pessoal tudo certo? Espero que sim!

Estes dias passei por um problema bem curioso envolvendo o SQL Server Agent de uma instância do SQL Server 2008, vou descrever abaixo o ocorrido e como cheguei a uma solução.

Havia um job no SQL Server Agent com um único step do tipo PowerShell. O job estava desabilitado já tinha um tempo, mas após uma solicitação para execução manual, falhou com o seguinte erro:

Unable to start execution of step 1 (reason: The PowerShell subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended).  The step failed.

Apesar do job ter falhado, em vez de ficar com o status de “IDLE”, ele ficou marcado como “SUSPENDED”. No Job Activity Monitor a opção de Stop Job ainda continuava disponível, mas ao ser acionada gerava uma mensagem de erro, indicando que o job não estava rodando:



No SQLAGENT.OUT, arquivo com o log de erros do SQL Server Agent, a seguinte mensagem estava registrada:

 [LOG] Step 1 of job ‘JOB_NAME’ (0xEE18D4FFB66BE246A781DACB9C1D0FB0) cannot be run because the PowerShell subsystem failed to load.  The job has been suspended

No início do SQLAGENT.OUT havia uma mensagem mais específica relacionada ao PowerShell:

 [432] There are 12 subsystems in the subsystems cache
 [125] Subsystem 'PowerShell' could not be loaded (reason: 3)

No SQL Server Agent, um subsistema é um componente de execução para o qual pode ser definido um Proxy (https://msdn.microsoft.com/en-us/library/ms174368(v=sql.110).aspx). No msdb existe uma tabela chamada syssubsystems, com todos os sub-sistemas que o SQL Server Agent suporta e informações sobre a sua execução. O PowerShell é um destes sub-sistemas, como pode ser visto na saída da query abaixo:

SELECT * FROM MSDB.DBO.SYSSUBSYSTEMS WHERE SUBSYSTEM = 'POWERSHELL'


A coluna agent_exe aponta para o sqlps.exe, utilitário que implementa o SQL Server PowerShell provider e suas cmdlets. No SQL Server Agent com o job em “SUSPENDED” o caminho informado para o sqlps.exe era inválido, o que explica a mensagem de erro indicando que o PowerShell não pôde ser carregado.

Pesquisando na Internet encontrei alguns sites mencionando a deleção de todas as linhas da tabela syssubsystems, e a execução da procedure sp_verify_subsystems para que ela populasse novamente esta tabela com as informações corretas dos componentes. Contudo, pelo fato de ser uma alteração em uma base de sistema, queria mexer o mínimo possível, portanto resolvi fazer o seguinte:

1. Backup full do msdb.

2. Alteração manual apenas da linha do Powershell na tabela syssubsystems, informando o caminho correto para o sqlps.exe:

USE MSDB
GO

UPDATE SYSSUBSYSTEMS SET AGENT_EXE = 'C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\SQLPS.exe' WHERE SUBSYSTEM = 'POWERSHELL'
GO

3. Restart no SQL Server Agent.

4. Conferir se o caminho do sqlps.exe ficou correto na tabela syssubsystems.

Depois deste procedimento, rodei o job novamente e obtive SUCESSO!

Testes e descobertas...

Simulando o cenário no SQL Server 2008

Vamos simular o cenário numa instância do SQL Server 2008, lembrando que a simulação vale também para um SQL Server 2008 R2.

1. Abra o Management Studio.

2. Crie um novo job no SQL Server Agent com um único step do tipo PowerShell, usando algum script simples que você possua No meu caso vou usar um script simples que lista todos os serviços do Windows. O meu step ficou da seguinte forma:




Salve o job sem criar nenhum schedule.

3. Rode o comando abaixo e salve o resultado:

SELECT agent_exe FROM MSDB.DBO.SYSSUBSYSTEMS WHERE SUBSYSTEM = 'POWERSHELL'

4. Faça um backup full do msdb, e rode o comando abaixo para mudar propositalmente o caminho do sqlps.exe.

UPDATE MSDB.DBO.SYSSUBSYSTEMS SET agent_exe = '***SQL MAGU***' WHERE SUBSYSTEM = 'POWERSHELL'

5. Reinicie o SQL Server Agent.

6. Confirme que o caminho para o sqlps.exe continua com a sua alteração.

SELECT agent_exe FROM MSDB.DBO.SYSSUBSYSTEMS WHERE SUBSYSTEM = 'POWERSHELL'


7. Agora tente executar o job. Note que o status é ajustado para “SUSPENDED”:



8. Clique em Close e consulte o histórico de execução. Note que o job falhou com um erro do sub-sistema do PowerShell:




9. Abra o Job Activity Monitor e confirme que o status do job é “SUSPENDED” e não “IDLE”. Apesar da falha, a opção de Stop Job continua ativa.




10. Vamos agora corrigir o problema. Rode o seguinte comando, trocando a parte em destaque pelo diretório correto do sqlps.exe na sua instalação:

UPDATE MSDB.DBO.SYSSUBSYSTEMS SET agent_exe = 'C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLPS.exe' WHERE SUBSYSTEM = 'POWERSHELL'

11. Reinicie o SQL Server Agent.

12. Verifique se o caminho para o sqlps.exe está realmente correto:

SELECT agent_exe FROM MSDB.DBO.SYSSUBSYSTEMS WHERE SUBSYSTEM = 'POWERSHELL'

13. Tente rodar o job novamente:




Sucesso desta vez! Problema corrigido!

Simulando o cenário no SQL Server 2012

A partir do SQL Server 2012 a coisa muda um pouco, no sentido dele ter diminuído a probabilidade destas inconsistências acontecerem. Para confirmar isto, execute o procedimento anterior até o passo 5. Note que apesar de você ter trocado o diretório propositalmente, após o restart do SQL Server Agent ele se acertou automaticamente! Mas por que isso aconteceu? Para tentar entender, vamos fazer o seguinte:

1. Crie um trace contra a sua instância do SQL Server 2012. Inclua apenas o evento de SP:Completed, com o nome da aplicação filtrado para “%SQLAgent%”.

2. Se o SQL Server Agent estiver rodando, reinicie-o e fique atento ao trace.

3. Note que ao iniciar o SQL Server Agent executa duas procedures:

sp_verify_subsystems
sp_enum_sqlagent_subsystems_internal

Ambas as procedures manipulam a tabela syssubsystems. Se você obtiver o mesmo com o SQL Server Agent do SQL Server 2008 também verá as mesmas procedures executando. Então o que mudou? No SQL Server 2012, obtenha o texto da procedure sp_verify_subsystems e repare no seguinte trecho:

-- Fix for #525111 - when MSDB is restored from any other sqlserver, it is possible that physical path to agent_exe, subsystem_dll may not be valid on current server
--  It is better to delete all records in this table and reinsert them again
-- perform delete and re-insert operations within a transaction
TRUNCATE TABLE syssubsystems

Pois é pessoal! Este truncate no código foi implementado do SQL Server 2012 em diante, notem que o comentário fala de um restore da MSDB, mas alerto que podem existir outros cenários onde isso pode acontecer, vejam abaixo alguns links que discutem problemas similares:

 


Era isso pessoal! A dica é sempre ficar atento quando estiver realizando reinstalações e manobras com instâncias de versões anteriores ao SQL Server 2012, com ou sem o restore do msdb.

Nenhum comentário:

Postar um comentário