主页 / dba / 问题 / 184327
Accepted
Henrik Staun Poulsen
Asked: 2017-08-25 23:01:29 +0800 CST

SQL 代理 PowerShell 任务永远不会完成

  • 772

我对 PowerShell 很陌生，现在我找到了 dbatools.io，我想在其中运行 SQL 代理中的 PowerShell 任务，例如

$ExportPath = $env:TEMP + '\DriveSpace.csv'
$datatable = Import-Csv $ExportPath | Out-DbaDataTable
Write-DbaDataTable -SqlServer MyServer -Database Utils -InputObject  $datatable -Table dbo.FreeSpaceOnDiskDrive -AutoCreateTable

它开始了，但它永远不会结束。我也尝试过执行以下任务：

Get-DbaDatabaseSpace -SqlServer MyServer -IncludeSystemDBs | Out-DbaDataTable | Write-DbaDataTable -SqlServer MyServer -database utils -Table dbo.DiskSpaceExample -AutoCreateTable

同样的问题。它在 Windows PowerShell ISE 中运行正常，但只是挂在 SQL 代理中。所以现在它为我创建了一个新表，并用数据填充了它，但工作只是继续运行。

sql-server sql-server-2016

4 个回答

  1. Best Answer

    您遇到的问题是 SQL Server 代理中的 PowerShell 子系统。由于您被置于 SQL Server PowerShell 提供程序 (SQLPS.exe) 的上下文中，因此使用其他模块有点不稳定。因此，它的工作方式与您打开sqlps.exe然后尝试执行代码的方式相同。

    使用dbatools模块要记住的一件事是，它将与 SQLPS 和sqlserverMS 现在为 SQL Server 单独维护的模块发生冲突。最后我检查了它与之冲突的主要内容是我们现在在模块中拥有的 TEPP，它只是无法加载该代码。[警告：我是这个模块的主要贡献者。]

    dbatools 模块具有内置的自定义类型和样式，因此当您在也具有 SQLPS 或导入 SQLServer 模块的 PowerShell 主机下运行脚本时，您的结果会有所不同。

    要在 SQL 代理步骤中使用 dbatools，请确保您只使用 CmdExec 子系统（步骤类型），然后调用 PowerShell 主机来执行您的代码。如果您不想为每个脚本维护一个文件，您可以按照下面说明的方式将代码放在 SQL 代理 CmdExec 步骤中，但更复杂的脚本更容易通过文件维护。

    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

    运行上面的工作在我的数据库和表中给了我这个db1.dbo.FreeSpaceOnDiskDrive

    在此处输入图像描述

    • 7

  2. 您误解了 SQL 代理的工作原理。powershell在实例外部的自己的窗口下运行。

    这意味着在操作报告完成或发生故障错误之前，powershell 操作将永久存在。如果您要尝试关闭会话，它将无限期挂起，直到您关闭 shell。在少数情况下，如果您没有正确停止 shell 触发的正确进程，这甚至可能是不可能的。

    例如，不要这样做，如果您使用暂停操作运行 powershell，那么您糟糕的 shell 将无限期挂起，因为您无法访问代理从中打开 powershell 的控制台。

    • 使用 SQLCMD

    这不仅在文档中得到支持，它还会在操作完成后特别关闭。甚至还可以设置各种步骤，文档丰富。

    • 继续使用powershell

    只是，硬编码退出路径。不要依赖操作返回给调用者。你确保它确实如此。

    事实上，这是编码的第一条规则：除非明确保证，否则不要假设您的代码有效。

    • 使用另一种方法。

    无论采用何种方法：DAC、ODBC、bcp、连接、集成服务 (SSIS) 连接

    SSIS 使用包，也非常适合重复操作。

    和更多...

    • 2

  3. 这些对我不起作用：

    • 出口
    • 退出-PSHostProcess

    一个有效的是：

    • [环境]::Exit(0)
    • 1

  4. 这是将数据库从一台服务器复制到另一台服务器的示例。

    您必须为代理作业使用 (cmd) 类型

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

    您还必须在服务器上安装 DBA 工具。使用 Powershell 只需运行以下命令： 安装 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'))

    安装 DBA 工具：

    choco feature enable -n useFipsCompliantChecksums
Choco install dbatools
    • 0

相关问题