A tarefa do SQL Agent PowerShell nunca termina

O problema que você está enfrentando é com o subsistema PowerShell no SQL Server Agent. É um pouco instável com o uso de outros módulos porque você é colocado no contexto do SQL Server PowerShell Provider (SQLPS.exe). Portanto, funciona da mesma maneira como se você abrisse sqlps.exee tentasse executar seu código.

Uma coisa a ter em mente com o módulo dbatools é que ele entrará em conflito com o SQLPS e o sqlservermódulo que o MS agora mantém separado para o SQL Server. A última vez que verifiquei a principal coisa que entrou em conflito foi o TEPP que temos no módulo agora, ele simplesmente não pode carregar esse código. [Aviso: sou um dos principais contribuintes para este módulo.]

O módulo dbatools possui tipos e estilos personalizados integrados, portanto, quando você executa os scripts no host do PowerShell que também possui SQLPS ou o módulo SQLServer importado, seus resultados variam.

Para utilizar dbatools em uma etapa do SQL Agent, certifique-se de usar apenas o subsistema CmdExec (tipo de etapa) e, em seguida, chame o host do PowerShell para executar seu código. Se você não quiser manter um arquivo para cada script, você pode colocar seu código em uma etapa CmdExec do SQL Agent da maneira ilustrada abaixo, mas scripts mais complexos são mais fáceis de manter por meio de arquivos.

USE [msdb]
GO

/****** Object:  Job [dbatools_example]    Script Date: 2017-08-30 8:53:15 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2017-08-30 8:53:15 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'dbatools_example', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [dbatools_command]    Script Date: 2017-08-30 8:53:15 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'dbatools_command', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'CmdExec', 
        @command=N'powershell.exe -ExecutionPolicy Bypass -Command "Import-Module dbatools; $server = ''manatarms''; Get-DbaDatabaseSpace -SqlInstance $server -IncludeSystemDbs | Out-DbaDataTable | Write-DbaDataTable -SqlInstance $server -Database db1 -Table dbo.FreeSpaceOnDiskDrive -AutoCreateTable"', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Executando o trabalho acima me dá isso no meu banco de dados e tabeladb1.dbo.FreeSpaceOnDiskDrive

Você não entendeu como o SQL Agent funciona. O powershell é executado em sua própria janela fora da instância.

Isso significa que até que a operação seja informada como concluída ou ocorra um erro de falha, a operação do powershell permanecerá perpetuamente. Se você tentasse fechar a sessão, ela travaria indefinidamente até que você fechasse o shell. Em alguns casos, isso pode até ser impossível se você não parar corretamente o processo correto do qual o shell está disparando.

Por exemplo, e não faça isso, se você executou um powershell com a operação de pausa, seu shell ruim ficaria travado indefinidamente, pois você não tem acesso ao console do qual o agente abriu o powershell.

• Usar SQLCMD

Não apenas isso é suportado diretamente nos documentos, ele será fechado especificamente após a conclusão da operação. Você também pode definir todos os tipos de etapas, e a documentação é rica.

• Continue o uso do powershell

Apenas, codifique um caminho de saída. Não confie em uma operação para retornar ao chamador. Você se certifica de que sim.

Na verdade, esta é a regra número um da codificação: não assuma que seu código funciona a menos que seja explicitamente garantido.

• Use outro método.

Qualquer que seja a abordagem: DAC, ODBC, bcp, conexões, conexões do Integration Services (SSIS)

O SSIS usa pacotes, ótimos para operações repetidas também.

E MAIS...

Estes não funcionaram para mim:

• saída
• Exit-PSHostProcess

Um que funcionou é:

• [Ambiente]::Sair(0)

Aqui está um exemplo de cópia de um banco de dados de um servidor para outro.

Você deve usar o tipo (cmd) para o trabalho do Agente

powershell.exe -ExecutionPolicy Bypass -Command "Copy-DbaDatabase -Source ServerA -Destination ServerB\InstanceName -WithReplace -Database DatabaseToCopy -BackupRestore -SharedPath ""\\backuphostname\Backups"""

Você também deve ter ferramentas DBA instaladas no servidor. Usando o Powershell, basta executar estes comandos: Instale o Chocolatey:

Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))

Instale ferramentas de DBA:

choco feature enable -n useFipsCompliantChecksums
Choco install dbatools