syspolicy_purge_history作业故障排除
描述
我们有一台数据库服务器windows 2012 r2 上有安装sql server 2012 和sql server 2016双实例，后续又把sql 2016的服务全部停用，即只保留sql 2012 的服务在用。在例行检查数据库的job 运行情况时发现syspolicy_purge_history该自带的job一直是失败的，错误一直停留在step 3，首先先了解一下该job的官方文档说明，发现虽然跟实际在用的业务功能没有什么关联，但还是觉得有必要修复。
操作步骤
a.查看job的具体报错提示
消息 已以用户 XXXXXXXXX 的身份执行。 作业步骤在 PowerShell 脚本的行 1 中接收到错误。对应行为“import-module SQLPS -DisableNameChecking”。更正脚本并重新安排作业。PowerShell 返回的错误信息为“未能加载文件或程序集“file:///C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll”或它的某一个依赖项。生成此程序集的运行时比当前加载的运行时新，无法加载此程序集。 ”. 进程退出代码 -1。. 该步骤失败。
查看该step 3 代码如下：
if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = '\DEFAULT'} ELSE {$a = ''}; (Get-Item SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()
其中对 ESCAPE_比较困惑，感觉不像是powershell ,查看帮助才知道是sql server agent 的tonkens.
b.在powershell 运行Import-Moduls 语句
还是一样无法加载该文件，那么升级到powhershell 5 能否解决该问题，因涉及到安装可能会重启服务器不确定因素，慎重起见该方案非首先选。竟然是无法加载该文件，那么直接使用sql 2012 对应的文件是不是就可以了，顺着这个思路，那么问题就变成了如何让step 3这步执行的默认去sql 2012的路径找。
c.修改环境变量
默认的内容
%ProgramFiles%\WindowsPowerShell\Modules;%SystemRoot%\system32\WindowsPowerShell\v1.0\Modules;C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\
修改为
%ProgramFiles%\WindowsPowerShell\Modules;%SystemRoot%\system32\WindowsPowerShell\v1.0\Modules;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\;C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\
即把sql 2016和sql 2012 的路径调换一下。
d.重新执行job验证
后记
后续遇到类似的syspolicy_purge_history的step3执行失败的案例A如下：
日期 2019/6/15 8:01:25 日志 作业历史记录 (syspolicy_purge_history) 步骤 ID 3 服务器 D********* 作业名称 syspolicy_purge_history 步骤名称 Erase Phantom System Health Records. 持续时间 00:00:00 SQL 严重性 0 SQL 消息 ID 0 已通过电子邮件通知的操作员 已通过网络发送通知的操作员 已通过寻呼通知的操作员 重试次数 0 消息 无法开始执行步骤 3 (原因: PowerShell 子系统加载失败 [有关详细信息，请参阅 SQLAGENT.OUT 文件]；该作业已挂起). 该步骤失败。
对应的SQLAGENT.OUT 文件部分内容如下：
2019-06-14 16:04:12 - ! [125] 无法加载子系统“PowerShell”(原因: 系统找不到指定的路径。) 2019-06-14 16:04:12 - ? [129] SQLSERVERAGENT 在 Windows NT 服务控制下启动 2019-06-14 16:04:12 - + [475] 没有为代理通知启用数据库邮件。 2019-06-14 16:04:12 - + [396] 尚未定义空闲 CPU 条件 - OnIdle 作业计划将不起任何作用 2019-06-15 08:01:25 - ! [LOG] 未能加载 PowerShell 子系统，所以不能运行作业“syspolicy_purge_history”(0x8CEE075FE296DF4DA6B50C671B32C757)的步骤 3。该作业已挂起。 2019-06-16 22:13:31 - + [000] 运行作业 syspolicy_purge_history 的请求(来自 计划 8 (syspolicy_purge_history_schedule))被拒绝，因为该作业已挂起 2019-06-17 07:40:44 - + [000] 运行作业 syspolicy_purge_history 的请求(来自 计划 8 (syspolicy_purge_history_schedule))被拒绝，因为该作业已挂起 2019-06-18 07:51:42 - + [000] 运行作业 syspolicy_purge_history 的请求(来自 计划 8 (syspolicy_purge_history_schedule))被拒绝，因为该作业已挂起 2019-06-19 07:41:52 - + [000] 运行作业 syspolicy_purge_history 的请求(来自 计划 8 (syspolicy_purge_history_schedule))被拒绝，因为该作业已挂起 2019-06-20 07:47:37 - + [000] 运行作业 syspolicy_purge_history 的请求(来自 计划 8 (syspolicy_purge_history_schedule))被拒绝，因为该作业已挂起
解决步骤
第一步：查询文件路径
--step 1 SELECT * FROM msdb.dbo.syssubsystems WHERE start_entry_point ='PowerShellStart'
第三步：查询sqlps.exe文件路径，如没有需其他服务器上拷贝同版本的文件。
第四步：修改路径
Use msdb go sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
UPDATE msdb.dbo.syssubsystems SET agent_exe='D:\Program Files\Microsoft SQL Server\130\Tools\Binn\SQLPS.exe' WHERE start_entry_point ='PowerShellStart'
SELECT * FROM msdb.dbo.syssubsystems WHERE start_entry_point ='PowerShellStart'
Use msdb go sp_configure 'allow updates', 0 RECONFIGURE WITH OVERRIDE
第五步：重启一下sql server 代理服务
第六步： 重新执行syspolicy_purge_history
发现还是挂起，当前使用的sql server 2016 ,重新查询路径发现又恢复成原始的路径，竟然走不通，那把文件直接拷贝到对应的路径下试试。重新按上述步骤走一遍；
发现还是失败，直接在路径中打开这个SQLPS.exe文件，发现如下错误
import-module : 未能加载指定的模块“SQLPS”，因为在任何模块目录中都没有找到有效模块文件。 所在位置 行:1 字符: 1 + import-module SQLPS + ~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ResourceUnavailable: (SQLPS:String) [Import-Module], FileNotFoundException + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand
对比其他服务器发现Tools文件夹下缺失PowerShell文件夹，从其他同版本的服务器拷贝一个过来，如下：
重复上述步骤再执行job
问题终于解决；
还有可能因其他原因出现上述的场景也可以参考这链接；
案例B
日期 2019/6/19 17:43:07 日志 作业历史记录 (syspolicy_purge_history) 步骤 ID 3 服务器 S***** 作业名称 syspolicy_purge_history 步骤名称 Erase Phantom System Health Records. 持续时间 00:00:30 SQL 严重性 0 SQL 消息 ID 0 已通过电子邮件通知的操作员 已通过网络发送通知的操作员 已通过寻呼通知的操作员 重试次数 0 消息 已以用户 HDJT\sprsql 的身份执行。 作业步骤在 PowerShell 脚本的行 1 中接收到错误。对应行为“set-executionpolicy RemoteSigned -scope process -Force”。更正脚本并重新安排作业。PowerShell 返回的错误信息为“安全性错误。 ”. 进程退出代码 -1。. 该步骤失败。
这个是sql server 2012版本；
官方的解决方法如下
SQL Server 2008年或 SQL Server 2008 R2 在无法启动 SQL PowerShell RemoteSigned策略。在 SQL Server 2008年中，因此，作业不会失败。作为一种安全措施，SQL Server 2012年启动 SQL PowerShell RemoteSigned策略中。这将导致作业失败，前面的问题发生。
不受限制的明确建议不要从安全角度因为这意味着没有限制。PowerShell 脚本运行成功时MachinePolicy设置为在域控制器中的RemoteSigned从 SQL 2012 年启动时，这就是原因。
要解决此问题，请使用下列方法之一︰
不受限制的明确建议不要从安全角度因为这意味着没有限制。PowerShell 脚本运行成功时MachinePolicy设置为在域控制器中的RemoteSigned从 SQL 2012 年启动时，这就是原因。
要解决此问题，请使用下列方法之一︰
- 不要设置域控制器 GPO 的计算机策略。如果未定义，这意味着下一个级别策略 （示例中， UserPolicy，然后流程，然后为CurrentUser，和上一次LocalMachine） 将优先。
- 在 Active Directory 用户和计算机中创建新组织单位 (OU) 并将链接组策略的 OU。然后启用 RemoteSigned 策略。若要执行此操作，请执行以下步骤：
- 转到Active Directory 用户和计算机。
- 用鼠标右键单击您的域->新建->组织单位创建新的组织单位。
- 在运行，键入gpmc.msc ，然后用鼠标右键单击组策略对象->新建以创建新的 GPO。
- 用鼠标右键单击新创建的 GPO->编辑。它会打开一个新窗口。
- 转到计算机配置->策略->管理模板-> Windows 组件-> Windows PowerShell -> 双击打开脚本执行
- 设置为允许本地脚本和远程签名的脚本的执行策略
- 单击应用，然后单击确定。
- 转到Active Directory 用户和计算机，然后单击计算机。在域中找到计算机的列表。右键单击您希望移动在新创建的组织单位中的计算机。以这种方式，可以将单个或一组计算机移动到一个组织单位。
- 转到组策略管理用鼠标右键单击新创建的组织单位、 单击链接现有的 GPO、 选择新创建的 GPO，然后单击确定。
- 通过运行此命令继续更新域控制器和客户端计算机上的策略。
gpupdate /force - 验证计算机策略的组织单位和客户端组件，它应该RemoteSigned。
