EPM_EnterpriseEvaluation_3.0.0.ps1 runs without error but PolicyHistory & EvaluationErrorHistory remain empty

Nov 14, 2011 at 10:34 PM

Hello everyone,

Sorry to bother you but I'm trying to implement the EPM Framework to monitor my SQL Servers and I'm not having much luck.  I'm running SQL Server 2008 SP2 Standard Edition 64 bit on Windows Server 2008 Enterprise.  My server is named "vmdev-app2" and I've upgraded my PowerShell to V2, created a new named instance called "CMS" to be the Central Management Server and created a new database within the CMS instance called "DBA".  I've registered the central management server and created a server group named "Development_2008" to which I've added the default instance (VMDEV-APP2) and another named instance (VMDEV-APP2\DEV01) running on this same host.  I've updated and successfully run EPM_Create_Database_Objects.sql and can see the tables and views in my "DBA" database.  Next, I unblocked the EPM_EnterpriseEvaluation_3.0.0.ps1 script by right-clicking and selecting "Unblock" then I edited the document to specify the following:

$CentralManagementServer = "VMDEV-APP2\CMS"
$HistoryDatabase = "DBA"
$ResultDir = "D:\MSSQL10.CMS\EPM\Results\"

Finally, I run SSMS and connect to the central management server. In the Object Explorer window on the left, right-click and select “Start Powershell”. Run the following commands:

 

SL D:\MSSQL10.CMS\EPM

.\EPM_EnterpriseEvaluation_3.0.0.ps1 -ConfigurationGroup "VMDEV-APP2\CMS" -PolicyCategoryFilter "" -EvalMode "Check"

The script runs without error but when I check the database, there's nothing there.

 

Use DBA
GO

SELECT * FROM policy.v_PolicyHistory
GO

PolicyHistoryID EvaluatedServer EvaluationDateTime EvaluatedPolicy PolicyResult ExceptionMessage ResultDetail EvaluatedObject policy_id CategoryName MonthYear
--------------- --------------- ------------------ --------------- ------------ ---------------- ------------ --------------- --------- ------------ ---------
(0 row(s) affected)

SELECT * FROM policy.v_EvaluationErrorHistory
GO

ErrorHistoryID EvaluatedServer EvaluationDateTime EvaluatedPolicy EvaluatedObject EvaluationResults policy_id   CategoryName MonthYear PolicyResult
-------------- --------------- ------------------ --------------- --------------- ----------------- ----------- ------------ --------- ------------
(0 row(s) affected)

Any recommendations on what to check?

Thanks in advance.

Ken

Coordinator
Nov 15, 2011 at 1:43 PM

Hello Ken,

You need to pass in the name of the CMS configuration group to the -ConfigurationGroup parameter.  If I read your implementation correctly, the script would be:

.\EPM_EnterpriseEvaluation_3.0.0.ps1 -ConfigurationGroup "Development_2008" -PolicyCategoryFilter "" -EvalMode "Check"

Or you can run it against all servers registered to the CMS by executing:

.\EPM_EnterpriseEvaluation_3.0.0.ps1 -ConfigurationGroup "" -PolicyCategoryFilter "" -EvalMode "Check"


HTH!!

~ Lara

Nov 18, 2011 at 5:20 PM

Hmm, I've tried both recommended solutions above but am still not getting any results.  I must be missing something - I just can't see what the problem is.

Ken

Nov 18, 2011 at 7:00 PM

I tried importing the "Database Auto Shrink" Policy and when I run the powershell script, I see the results of "Database Auto Shrink" in PolicyHistory but not any results for the Policy I created following the steps shown here:

http://www.sqlservercentral.com/articles/PBM/68602/

It appears the problem is my policy although when I compare "database auto shrink" and "database free space threshold", I don't see anything that would explain the difference.

Ken

Coordinator
Nov 18, 2011 at 7:03 PM

You may want to check that the policy does not have any filters.  For example, if it has a filter so it only checks on databases in simple recover, and all of your databases are full recover, then the policy would complete but you would not see results.

Nov 18, 2011 at 7:08 PM

My mistake:  I was querying the view v_PolicyHistory.  When I check the table PolicyHistory, I see results for my free space policy.  The problem is that there are no rows for my policy in the PolicyHistoryDetail table.  My investigation continues.

Ken

Nov 18, 2011 at 7:43 PM

FOUND IT! 

Once I opened my policy, went to the description, and changed the Category from <default> to a newly created category (I used "Custom Policies"), it works perfectly and I get results in both PolicyHistory and PolicyHistoryDetails tables.  The view v_PolicyHistory works too.  Thanks for your help.

Ken