I spent an annoying afternoon with SQL Server 2008 today. When we installed it on our cluster it created a job called “syspolicy_purge_history”. I assume it does this in most installations but I haven’t checked.
The job was generating an error every time it ran. A quick Google search turned up an article on the problems with syspolicy_purge_history and clusters. Now all I needed was to figure out the PowerShell syntax to correctly refer to this instance.
PowerShell? Oh yes. They couldn’t just call a stored procedure. Oh no. Rather than giving me the choice to learn and then use PowerShell I’m being forced to wade through the syntax while troubleshooting. I’m not exactly happy with that decision right now. I thought this software was supposed to make me productive? Was PowerShell really needed for this?
The KB article does a good job explaining what to do if you’re running on a named instance but there wasn’t an explanation on what to do if you’re not running on a named instance. Technically I guess our instance was named MSSQLSERVER but that didn’t work. It turns out you need to add the keyword DEFAULT as I did below.
(Get-Item SQLSERVER:\SQLPolicy\DBCLUSTER1\DEFAULT).EraseSystemHealthPhantomRecords()
I really hope this job truly needed PowerShell.