SysPolicy_Purge_History failing SQL Server 2017
-
Greetings,
I've installed SQL 2017 for the first time and noticed that the built in job syspolicy_purge_history keeps failing on step 3 (Erase Phantom System Health Records).
Error Message:
Executed as user: <Domain Account>. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS -Version 14.0'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The member 'ScriptsToProcess' in the module manifest is not valid: Cannot find path 'C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1' because it does not exist.. Verify that a valid value is specified for this field in the 'C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1' file. '. Process Exit Code -1. The step failed.
Since the ScriptsToRun in the manifest is pointing to SQLPS.ps1, I've confirmed that the file is not in the path mentioned above. When I look at all of our other SQL Servers, that ps1 file is in fact there.
I did try Install-Module
-Name SqlServer
-Force –AllowClobber
without any change. Here is what I get when I list the sqlserver modules:
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 21.1.18179 SqlServer {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvail...
Script 21.1.18147 SqlServer {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvail...
From what I've been reading, SQLPS is actually no longer a thing and is superseded by SqlServer. Does that mean SQLPS.ps1 is no longer installed. If not, how does one clear up the issue?
Thanks!
-
Change the job owner to "SA"
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Appears it already is...
-
look at https://dba.stackexchange.com/questions/171395/troubleshooting-powershell-errors-from-sql-agent-job
Possible solution is to change that step to be a cmd file instead of direct posh
-
That example looks like the step is calling a specific .ps1 script. Looking at the 3rd step, it doesn't appear to be like that:
I'm not sure how one would shoehorn powershell.exe into the code above. I did try the following after flipping to the type to CMD :
powershell.exe "if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = '\DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()" -noninteractive
That specific step did pass this time however, when I ran it, it was instantly done. Which makes me wonder if it just ran powershell.exe and then closed.
-
There is an environment variable PSModulePath that should be pointing to whatever location has the folder structure: PowerShell\Modules\SQLPS
I'd check your environment variable and check for the location of the SQLPS folder. It may just be in a different location and needing the environment variable updated for the location.
Sue
-
Thanks Sue for the suggestion.
I did a search on the server for SQLPS. The only location where there is a SQLPS folder is at C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules. Checking the environment variables and I do see that that path is already entered.
I think we might have it working maybe. When we look at the SQLPS.ps1 file from some of our other older SQL servers, there are just a couple lines of Set-Variables followed by a large signature block.
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000
What we noticed looking a little closer, each line of the sig block had a preceding hash (#). Perhaps the sig block is commented out? So for fun we copied over SQLPS.ps1 from another server and the error went away! We also removed the sig block just to see if it made a difference. Everything is still all green.
However, even though the job activity shows that the task is not running, the history of this looks a bit strange:
Step 3 now comes up twice. One with a green triangle and another with the green checkbox. I looked at one of our other SQL servers and their history does not have a duplicate step 3.
Thoughts?
Thanks!
-
Nevermind. Co-worker, who was also working on this, had the "Include step output in history" checked for that step. Now if we can determine if this task is really doing what it is supposed to be doing....
-
If you are using one where you deleted the signature block, it's not doing anything. That block is just the code encrypted. Use a version with that block included.
Sue
-
Did some more looking into this job. The agent job (syspolicy_purge) cleans up the history of the system policies that were run. The history is stored in the msdb.dbo table. When the policy is run, it saves the history. This agent job cleans this history. The retention interval can be set for how long the history will be stored. If it's set to 0, it won't be removed. IT looks like the default is 0 when I looked a couple of our SQL servers.
Also, when I look at the history, there is total of 1 entry listed. So it would appear that with default settings set, this step is pointless.
Thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply