Wednesday, February 17, 2016

"The syspolicy_purge_history SQL Server Agent job may fail in SQL Server 2008"

... and in SQL Server 2012. Doubt I've found a v2012 bug, but I've definitely found the same failure there. My customer's syspolicy_purge_history job failed last night with this error:

A job step received an error at line 1 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\CLUSNOD3\VISION).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Failed to connect to server .
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)


I'll bet the server wasn't found because 'CLUSNOD3\VISION' does not exist. This occurred on SQLPROD\VISION, where 'CLUSNOD3' is the name of one of the cluster nodes. See Step #6 in the 1st method of correction suggested by MSFT in this Kb:     https://support.microsoft.com/en-us/kb/955726

Easy fix. Just "replace the computer node name by using the virtual server name for the cluster instance".  Ok.  But how do you know for sure what the virtual server name is? Run this on the instance where the failure occurred:

     SELECT SERVERPROPERTY('MachineName')     

The 'MachineName' property returns the computer name on which the SQL Server instance is running. If it is a clustered instance, it returns the name of the virtual server.  My result is 'SQLPROD'. 

Exactly as outlined in the 955726 Kb, I edited step #3 of the sysolicy_purge_history job, replacing 'CLUSNODE3' with 'SQLPROD', like this:

   (Get-Item SQLSERVER:\SQLPolicy\SQLPROD\VISION).EraseSystemHealthPhantomRecords()

The job now completes without error.

Not sure how the incorrect servername got in there, but that's another issue entirely... The syspolicy_purge_history Agent job was introduced with v2008 with Policy Based Management.  Take a look at these for details regarding syspolicy_purge_history, and Policy Based Management in general:

   https://msdn.microsoft.com/en-us/library/dd795279.aspx
    https://msdn.microsoft.com/en-us/library/bb510667(v=sql.105).aspx





No comments:

Post a Comment