"syspolicy_purge_history" Job is failing with error "PowerShell subsystem failed to load"

Sometimes the SQL server Job syspolicy_purge_history will fail with the below error 

"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." 

The Cause of this failure is the invalid location of SQLPS.exe file. We can check the current path SQL server is using for SQLPS.exe using below query:

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

We can go to above location and can check SQLPS.exe exists or not.If not we can simply search the SQLPS.exe in our system and can copy it to above location.

Or we can update the location of it.


Use msdb
go
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE

--If the path is "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\SQLPS.exe"

UPDATE msdb.dbo.syssubsystems SET agent_exe='C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\SQLPS.exe' WHERE start_entry_point ='PowerShellStart'

sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE


Now restart the SQL server agent and try again to run the job.

Thank You!!

Error 21.The device is not ready in SQL Server error log file.

It was Friday, evening shift, I got a ticket of Backup job failure on my production database. I login to that server and checked the Job history It was failing due to compression error. I checked the version etc and everything was fine. I decide to take a differential backup with script. It also got failed with error "BACKUP DATABASE is terminating abnormally" 


Although this was not related to space issue but still i checked for disk space and there was plenty of free space. I decide to check the error log file and there it was....

"The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000e56000 in file 'H:\MSSQL\Data\XXX.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online."

Since the error message says that the drive on which data file resided was not ready, it was too obvious that there was some storage problem. I checked the system event log but did not found any error related to disk. The database was online and accessible. But when I tried to access the Properties of the databases, the same error was thrown. I was not able to see the user tables under the databases. But the other queries like sp_helpfile and select * from sysobjects were executing successfully. When I tried to access a user table by script it was failing with the same error.

I sent an email to storage team and they confirmed that everything was fine from there side.

There were two options with me to fix this issue at that time. 1: Take the database offline and bring it online. 2: Another was restart the SQL server. There were more than one databases on my SQL server instance so I restarted the SQL server service. The database came online and the *device not ready* error message disappeared. 

The next action you can take is to run DBCC on your database.

Thanks!!