SQL - List Jobs in System For Current Owner

SELECT s.name AS JobName, l.name AS JobOwner

FROM msdb..sysjobs s

LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid

WHERE l.name IS NOT NULL

ORDER by l.name

What does “syspolicy_purge_history” do?

To answer this question, we started from SQL SERVER 2008 which introduced a new feature called Policy Management. For example, one could define a policy that says all tables must have a clustered index. Once defined, the policy can be evaluated against targets to check for compliance. Each evaluation of compliance will be recorded in tables in MSDB,

i.syspolicy_policy_execution_history_details_internal,

ii.syspolicy_policy_execution_history_internal, and

iii.syspolicy_policy_category_subscriptions_internal.

The evaluation condition is built on top of object facets, which is predefined by Microsoft.

The purpose of that job, syspolicy_purge_history, is to remove records in those tables to maintain so it does not pile up and take up space unnecessarily.

It has 3 steps:

1.Firstly, check if Policy Based Management System is enabled and exit if it is not.

2.Run the msdb.dbo.sp_syspolicy_purge_history system stored procedure to clear the history

3.Use PowerShell to clear the system health phantom records