Powershell - Invoke-PolcyEvaluation always returns false

Question

I dont' know if this is the right forum but since it is SMO behind the scenes, I thought it may be. 

I am trying to figure out what is wrong with this piece of code. I have a condition that checks the data space used against the max size. The policy looks for data that stays below 90% of max. If it is reaches 90% or more this policy should return a Result of False. The problem I am having is that every database I try returns False whether or not the policy is violated. Evaluating the Policy in SSMS works just fine as does TargetServer but I need to find the individual databases that have issues.

If anyone has any ideas, I would really appreciate it.

$db=gi sqlserver:\sql\D000XXX\default\databases\Northwind

gci -path SQLSERVER:\SQLPolicy\D000XXX\default\Policies |
where {$_.Name -eq 'MaxSizeDataFile'} |
Invoke-PolicyEvaluation -TargetObjects $db |
ft Result -autosize

I ran this to see if there was an exception

Invoke-PolicyEvaluation -TargetObjects $db |
foreach-object {$_.ConnectionEvaluationHistories} |
foreach-Object {$_.EvaluationDetails} |
select TargetQueryExpression, Result, Exception

This is the exception:

Microsoft.SqlServer.Management.Dmf.PolicyEvaluationException: Exception encountered while executing policy 'MaxSizeDataFile'.  ---
> Microsoft.SqlServer.Management.Dmf.MissingTypeFacetAssociationException: There is no association between type 'Database' and facet 'DataFile'.
at Microsoft.SqlServer.Management.Facets.FacetRepository.VerifyAssociation(Type target, Type facet)                            
at Microsoft.SqlServer.Management.Facets.FacetRepository.GetAdapterObject(Object target, Type facet)                           
at Microsoft.SqlServer.Management.Facets.FacetEvaluationContext.GetFacetEvaluationContext(String facetName, Object target)     
at Microsoft.SqlServer.Management.Dmf.Condition.Evaluate(Object target, AdHocPolicyEvaluationModeevaluationMode)  

I kind of understand the message but if that is truly the case, how can one evaluate this policy/condition against individual databases through Posh to find out which one(s) violate the policy?  

Answer 1

Hi greenie,

>> Evaluating the Policy in SSMS works just fine as does TargetServer but I need to find the individual databases that have issues.
To verify the state based on a specified policy for each individual database in the instance, you may need to loop all of databases to issue the Invoke-PolicyEvaluation. The sample code is used to display the results for every database according to the Database Page Status policy.

#please replace the instancename and policy directory
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "instancename" 
foreach ($db in $srv.Databases)
{
    sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033" # the policy directory  
    Invoke-PolicyEvaluation -Policy "Database Page Status.xml" -TargetServerName "instancename" -TargetExpression "Server[@Name='instancename']/Database[@Name='$dbname']" 
}

To output the result for each database, please follow the syntax below:

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "instancename"
foreach ($db in $srv.Databases)
{
    $output="d:\"+ $db.name +".xml" #define the file path with file name based on the database name
    sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"
    Invoke-PolicyEvaluation -Policy "Database Page Status.xml" -TargetServerName "instancename" -TargetExpression "Server[@Name='instancename']/Database[@Name='$dbname']" -OutputXML >$output
}

 

---

Stephanie Lv

TechNet Community Support

Answer 2

Hi Stephanie,

Thanks but I have tried that variation also but had no luck with it.

---

greenie

Answer 3

Hi greenie,

The code above can be run successfully by launch SQL Server powershell to check the state of Database Page Status policy. If it can work at the same situation, then the problem might be related to the definition of the MaxSizeDataFile policy.

---

Stephanie Lv

TechNet Community Support

Answer 4

Hi Stephanie,

this is the expression in the condition:

Multiply(Divide(@UsedSpace, @MaxSize), 100)

and the condition is that the result of the expression needs to be less than 90

As I said in an earlier post, it evaluates properly in the SSMS gui.  The same holds true for the Database Page Status. I created a corrupt database (using a method from one of Paul Randalls blogs). It evaluates properly in the gui but not in a Powershell script using the code you provided.

---

greenie

Answer 5

I have narrowed this down to an issue with the foreach loop. If I evaluate a policy on an individual database using the Invoke-PolicyEvaluation it will return the correct Result. The issue only occurs in the foreach loop which leads me to believe that the first iteration of values isn't being refreshed. I added a $db.refresh() prior to the Invoke statement but that didn't make a difference.

---

greenie