Microsoft SQL Server Agent jobs may fail due to path inconsistency within the MSDB subsystem table between cluster nodes.
This problem is typically caused by installing Microsoft SQL Server Shared Components to different paths across the cluster members. So once an instance fails over to another node where the shared components were installed to an alternate location, the paths defined within the MSDB subsystem table are no longer valid for that node.
One common error you will see is the syspolicy_purge_history job failing because "PowerShell subsystem failed to load".
The following script will update the usp_reset_subsystem_path stored procedure. You can download the script here (remove the .txt off of the end of the filename after downloading it):
You will need to run this script on each of your active instances. You will also need to replace the resetsubsystem.sql file in the DH2i bin directory on every node (%ProgramFiles%\DH2i\bin). This will ensure that any new instances that are virtualized will get the updated stored procedure automatically.
The instance subsystem table will be updated automatically on the next rehost or failover, but if you’d like to fix the subsystem table immediately on a given instance, then after deploying the update script you will need to execute the following:
- DxEnterprise 15.0
- DxEnterprise 15.5
- DxEnterprise 16.0
- Microsoft SQL Server