"syspolicy_purge_history" "PowerShell subsystem failed to load"

Question

Post reply

"syspolicy_purge_history" "PowerShell subsystem failed to load"

Jon.Morisi

SSChampion

Points: 12846
More actions
August 18, 2009 at 9:37 am

#140972

Anyone seen this error logged on a SQL 2008 Cluster?
"syspolicy_purge_history" "PowerShell subsystem failed to load"
Message
The job failed. The Job was invoked by Schedule 8 (syspolicy_purge_history_schedule). The last step to run was step 3 (Erase Phantom System Health Records.).
Job status is listed as suspended in Activity Monitor
This looked like it could possibly be related http://support.microsoft.com/kb/955726

Viewing 15 posts - 1 through 15 (of 54 total)

You must be logged in to reply to this topic. Login to reply

Govind-326906 SSC Journeyman Points: 86 More actions · Answer 1

check this...

Procedure to trouble shoot the job ‘syspolicy_purge_history’ on SQL server

Some times Job--syspolicy_purge_history will fail with the below error

Message

Unable to start execution of step 3 (reason: The PowerShell subsystem failed to load [see the SQLAGENT.OUT file for details];

The job has been suspended). The step failed.

Cause of the failure : This is due to invalid location of SQLPS.exe file

To trouble shoot the issue please follow the below steps.

1.by using the below script check the location of SQLPS.exe file .

SELECT * FROM msdb.dbo.syssubsystems WHERE start_entry_point ='PowerShellStart'

2.Go to the server and check whether the file ‘SQLPS.exe’ is located in the path as per step1.

3.In this case normally the two paths will be different.

4.Enable updates using the below script

Use msdb

go

sp_configure 'allow updates', 1

RECONFIGURE WITH OVERRIDE

5. Update the correct path

/* Update to correct path

Here “E:\Server_apps\x86\MSSQL\100\Tools\Binn\SQLPS.exe” is given as path. Change it according to the location of SQLPS.exe file

*/

UPDATE msdb.dbo.syssubsystems SET agent_exe='E:\Server_apps\x86\MSSQL\100\Tools\Binn\SQLPS.exe' WHERE start_entry_point ='PowerShellStart'

Execute the above script after necessary modification( if required) in msdb database.

6. Disable updates using the below script

/* Dis-allow updates to system tables */

sp_configure 'allow updates', 0

RECONFIGURE WITH OVERRIDE

7. Confirm that SQLPS.exe file path has changed by running the below script once again

SELECT * FROM msdb.dbo.syssubsystems WHERE start_entry_point ='PowerShellStart'

8. Restart the respective SQL agent ( if it is clustered then restart it from the cluadmin )

9. Re run the job.

Jon.Morisi SSChampion Points: 12846 More actions · Answer 2

looks like the fix in http://support.microsoft.com/kb/955726 solved my problem. The scheduled task completed successfully last night. Thanks for that additional post. Where did that come from?

S. Kusen SSChampion Points: 10936 More actions · Answer 3

Govind -- that fix was perfect.

For whatever reason, maybe I missed a step in the add-node wizard, the binaries were put on the C:\Program Files (x86) on the passive node instead of the D:\Program Files (x86) which is where it is on the primary node.

After installing a second instance on the cluster, this syspolicy_purge_history job was failing with this error and updating that entry worked like a charm

Have a good one!

Steve

dreamthebest Ten Centuries Points: 1324 More actions · Answer 4

How to provide SQLSERVERINSTANCE NAME if it is a two word name something like ADAM\SANDLER

since it will take it as path in the PowerShell Script as the syntax is :

SQLSERVER:\SQLPolicy\SQLSERVERINSTANCE\DEFAULT).EraseSystemHealthPhantomRecords():-)

S. Kusen SSChampion Points: 10936 More actions · Answer 5

Instead of "DEFAULT", I would say it's the instance's name. I don't know that first-hand though.

dreamthebest Ten Centuries Points: 1324 More actions · Answer 6

Yeah!! thats right. The first one is virtual name and the second is instance name.

Anyway I got the fix. It was to use as ADAM(my virtual server name)\SANDLAR(my instance name)

Thanxs every1.:-)

Mr. SQL Guy Mr or Mrs. 500 Points: 517 More actions · Answer 7

Hi everyone,

As S.K. noted above, if you install the SQL shared tools in a nonstandard directory on the 1st node in your cluster, when you add the 2nd node to your cluster it puts the shared tools in the DEFAULT directory not the one you specified on your first node. This means that the Powershell subsystem will only work on one of the nodes not the other. I called Microsoft support about this issue and after working with me they acknowledged that it's a bug that will be fixed in SQL Server 2008 SP2. They offered 2 workarounds. Below is Microsoft's response with the 2 workarounds (we are leaning toward option 2):

=================

It was my pleasure to work with You during your Microsoft SQL Server issue. As per our conversation, since this is a known issue with the SQL 2008 cluster, we will go ahead and Refund this case.

Also, here are the workarounds we talked about.

[...]

Assessment/Troubleshooting:

========================

Workaround:- There are two workarounds.

1. This is pretty similar to - http://support.microsoft.com/kb/903205, except the scenario is different.

Run the below commands and this will correct the subsystem paths - this will break again once SQL goes back to the other node. It’s possible that someone might consider to have a procedure do this and have the proc execute at the startup.

use msdb

go

delete from msdb.dbo.syssubsystemsexec

msdb.dbo.sp_verify_subsystems 1

2. Other and slightly more time consuming option is to - Remove the node [where the shared components are NOT in C:\program files] and just add it back again. Just remember you need to cleanup SQL from that node in entirety.

Note: Just FYI - This is scheduled to be fixed in SP2.

Regards

[...]

SQL Server Support Engineer

--Mr. SQL Guy

S. Kusen SSChampion Points: 10936 More actions · Answer 8

Thanks for the follow up to this. It's good to know that it will be fixed in SP2.

Mr. SQL Guy Mr or Mrs. 500 Points: 517 More actions · Answer 9

btw, I told MS Support they need to change their cluster install documentation to warn people about putting the shared tools in a nonstandard (i.e. non-default) directory, and they said they would do that. I'm going to look at the documentation now to see if they did. If they didn't, I'll send them an email. My case with them is still open.

--Mr. SQL Guy

SQLBalls SSCarpal Tunnel Points: 4621 More actions · Answer 10

Thanks Govind-326906, just came across this thread, my Node2 was missing the x86 folder all together. I copied it over from Node 1 and restarted the SQL Agent from Failover Cluster Admin, and was able to get the job to run successfully.

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field

Mr. SQL Guy Mr or Mrs. 500 Points: 517 More actions · Answer 11

Bradley B (4/21/2010)
...my Node2 was missing the x86 folder all together. I copied it over from Node 1 and restarted the SQL Agent from Failover Cluster Admin, and was able to get the job to run successfully.

I would not recommend doing it that way. When I talked to the MS engineers they said the only good way is to remove the first node, uninstall SQL Server, then add the node back in. Don't forget that there may be registry settings, registered COM components, etc., that you're not taking into account. You may have inexplicable problems down the road with just a file copy like you did.

--Mr. SQL Guy

SQLBalls SSCarpal Tunnel Points: 4621 More actions · Answer 12

Trust me I completely agree.....that would not have been my first option. This was a cluster where I set up and documented the whole process, at the end of the day we just need to add Node B, I said to my System Admin, we'll finish this up tommorrow.

..he was impatient and flipped through it himself, when I came in everything looked okay, I was pissed but it looked okay, so it got moved into production. Because of this I have discovered that even though I put the x86 drive on E, he left it to its defaults and on the other Node it is on C.

looking through regedit on both nodes I can see the differences, I reported it up the chain and the answer I got back was, we cannot take the redundancy offline to fix it at this time.

but it would have been harder to figure this out without looking at the MSDB database to see where the path "should" have been posted.

....In a perfect world people would listen to the DBA... in an imperfect world you get copy and paste.

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field

S. Kusen SSChampion Points: 10936 More actions · Answer 13

Until SP2 comes up, when you add the node, it will go on C:\ anyway. There's no way to specify otherwise.

So, if you put all the shared components on E:\ on node A, then add node B, the shared components will still go to C:\.

I read on this board (didn't go back through all of the other posts), that this issue is supposed to be resolved when SP2 ships.

Regards,

Steve

zaubut SSC-Addicted Points: 488 More actions · Answer 14

Mr. SQL Guy

I found your information to be most helpful.

As opposed to using the solutions you outlined, i have a question. Wouldn't it be easier to just copy the c:\Program Files (x86)\Microsoft SQL Server\100 to d:\Program Files (x86)\Microsoft SQL Server\100 on the passive node?

That way the files exist in both locations and should be able to be located by SQL. On my active node i do have a d:\Program Files (x86)\Microsoft SQL Server\100 directory but on the passive node i do not. So I wouldn't think there would be any harm in creating it and copying the data.

this is a production SQL farm I'm working with so pulling a node out of production isn't exactly the ideal solution. Solution 1 only works presuming all instances are on their active nodes.

thoughts?