Syspolicy Purge History job getting failed

Hi Friends,

This is a very simple post where I will provide  a solution for the failure of the job "syspolicy_purge_history". 

In my environment we had SQL 2017 on one of the machines and later I was asked to install SQL 2012. After the installation as you know, this job would get created automatically.

This job contains 3 steps out of which the job is getting failed at the 3rd step with the below error message

The error information returned by PowerShell is: 'Could not load file or assembly 'file:///D:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll' or one of its dependencies.

It can be fixed quite easily by just changing the environment variables. When it comes to root cause this happened as the newly installed SQL Version (lower)  PowerShell module being sit to the bottom of the PowerShell Module list. As a result, it looks for higher version when executing the Powershell Script.

select the PSModulepath and click on Edit button

As you can see from the above image the 2017 version is sitting on top of 2012 hence we just need to move up 2012 ahead of 2017 to overcome this error.

I believe sometimes small tips will save lot of time hence sharing this. 

Thanks for Reading.


Mohammad said…
Hi Vamsy Can you write a blog on ways to get database backup status for a list of SQL Server instances at once.