I hope this is the right place to ask this question.
I have been running EPM since May 2011. My database is currently massive, with PolicyHistoryDetail taking up most of the space. It is 130GB in size. The PolicyDashboard report errors out, saying that the user cancelled the request. In actuality,
it just times out.
The first SQL it runs is:
declare @ServerGroupName nvarchar(13)
select @ServerGroupName=N'SQL 2005 PROD'
SELECT CASE WHEN EXISTS (SELECT *
FROM policy.v_PolicyHistory_LastEvaluation iple WHERE iple.EvaluatedServer = ple.EvaluatedServer
AND iple.PolicyResult = 'fail') THEN 'Out of Compliance'
ELSE 'In Compliance' END AS InstanceStatus
FROM policy.v_PolicyHistory ple
WHERE (EvaluatedServer IN (SELECT server_name FROM policy.pfn_ServerGroupInstances(@ServerGroupName))OR @ServerGroupName = '')
GROUP BY EvaluatedServer
When I run this SQL manually, it takes 55 minutes to return, hence the time out on the report.
Is it necessary for any reason to keep all the info in the PolicyHistoryDetail table, or would keeping the last 30 days be sufficient. NOTE: There is no business pressure to run EPM. I am doing it to keep ensure that our SQL Server instances are built
uniformly and no security guidelines are violated. I also use it for monitoring, to a very lesser degree.
I guess what I am trying to ask is whether there are any information in these older rows that is needed should I run a report today?
Thanks for the help so far with other issues