Erase phantom system health records ошибка

Question: In SQL Server 2008 there is a SQL Server Agent Job called syspolicy_purge_history . Step 3 — Erase Phantom System Health Records is failing with reason 193. I have a daily Powershell script — List failed SQL server Jobs with Powershell which reports the failure

How can I fix?

Answer:Look at the command in the Job Step. It will be something like:

 It’s attempting a mapping to the SQL Server Instance Path. Which is probably something like:

 (Get-Item SQLSERVER:SQLPolicyMyServerInstance).EraseSystemHealthPhantomRecords()

 C:Program Filesxxxxxxx

 Look at the C: , is there a file called “Program” ? Not a directory , but a file. If so , rename it and rerun the job. It should fix the error .

If this solution does not fix the reason 193 issue on Erase Phantom System Health Records, contact me and I’ll help you out

Related Posts

How to request SQL Server troubleshooting

SQL Server Agent Job Steps

List failed SQL server Jobs with Powershell

Author: Tom Collins (http://www.sqlserver-dba.com)

Share:

Your comment could not be posted. Error type:

The SQL
Agent Job «syspolicy_purge_history»
was failing in our environment since we have upgraded our SQL server
instance from SQL 2008R2 to SQL2012. The job was failing at step-3 named as
«Erase Phantom System Health Records» We have tried each and every
article available over internet, but nothing was helpful in our case. After a
lot of troubleshooting my friend Desh found the solution for it. 

There
are still lot of DBA’s searching the fix of same issue. So, I want to share the
all steps which fixed the issue. You can try it in your case
and let us know if this works.

Issue
Details:

SQL
Server version: SQL Server 2012

Job
Name: syspolicy_purge_history

Error:

Executed as user: «XXXXX». The job script encountered the following errors. 
These errors did not stop the script:  A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘import-module SQLPS  -DisableNameChecking’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘The specified module ‘SQLPS’ was not loaded because no valid module file was found in any module directory.  ‘  A job step received an error at line 2 in a PowerShell script. The corresponding line is ‘(Get-Item SQLSERVER:SQLPolicyServerName$a).EraseSystemHealthPhantomRecords()’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot find drive. A drive with the name ‘SQLSERVER’ does not exist.  ‘  A job step received an error at line 2 in a PowerShell script. The corresponding line is ‘(Get-Item SQLSERVER:SQLPolicy

Steps
to Fix the issue:

The first step is to check if SQLPS folder exists to below
location:

C:Program
FilesMicrosoft SQL Server110ToolsPowerShellModules

Now Open Computer Properties
and then click on advance system settings> environment variables> Copy
the environment path to a notepad file

%ProgramFiles%WindowsPowerShellModules;

%SystemRoot%system32WindowsPowerShellv1.0Modules

Now copy the SQLPS
folder from location “C:Program FilesMicrosoft SQL
Server110ToolsPowerShellModules“ to below locations which we found under
environment variables:

 %ProgramFiles%WindowsPowerShellModules

                                           %SystemRoot%system32WindowsPowerShellv1.0Modules

You can Open the above two locations via start>run>
and then paste location and then Enter.

Once this folder is copied to these locations. Try to run the
below command from Powershell.

Open Powershell  as
admin and then execute the below command.

import-module
SQLPS  -DisableNameChecking

Once
import module is done. Open the Group Policy Object Editor as below:

1.  Start > run > «mmc»
2.  File > Add/Remove Snap-In…
3.  Under Available snap-ins, select «Group Policy Object Editor» and click Add, then Finish.
4.  Click OK to snap-in the GPO Editor

Now navigate to the specific policy:

1.  Console Root > Local Computer Policy > Computer Configuration > Administrative Templates > Windows Components > Windows Powershell
2.  Open-up the «Turn on Script Execution» policy setting
3.  Enable Script Execution and in Options select “ Allow All Scripts”. Apply the setting and click on OK.
4.  Exit from this window

Below
is the screen shot for above steps for your reference:

Exit from this window and during save pop-up dialog click on
No.

Now Try to run the Job it should be running without any issue.

Hope this works in your case.

I have a SQL Server named instance that runs the standard syspolicy_purge_history job every night. The job is marked as succeeding every night, but every night on the DEFAULT instance on the same server, there is a failed login connection from the service account of the named instance.

I’ve tracked it down to the Erase Phantom System Health Records step of the syspolicy_purge_history job. The job uses the standard code and does not appear to have been modified in any way:

if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = 'DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:SQLPolicy$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()

There are two other named instances on the same server, running the same job with the same code, and they do not generate failed login errors on the default instance.

I tried deleting the job, and recreating it by scripting the job from one of the other named instances. It made no difference.

I tried changing the code of step 3 to:

(Get-Item SQLSERVER:SQLPolicy[MyServerName][NamedInstanceName]).EraseSystemHealthPhantomRecords()

and alternatively to:

(Get-Item SQLSERVER:SQLPolicy[MyServerName][NamedInstanceName]`,[NamedInstancePort]).EraseSystemHealthPhantomRecords()

Both versions still generate the failed login on the default instance.

Both versions of the command appear to work successfully if I run them from a powershell window, and do NOT generate a failed login.

I cannot think of anything else to try. Google seems to be out of suggestions for me too.

Any ideas?