Only a small fraction of DBs being evaluated by the Policies

Nov 22, 2011 at 6:16 PM

I've loaded two MS policies ("Database Auto Shrink" and "Data and Log File Location") and one custom policy (Datafile_FreeSpace_Threshold_Policy - from http://www.sqlservercentral.com/articles/PBM/68602/).  I have 42 non-system databases on the default instance but when I run the powershell job I only get results from a fraction of the databases.

Source Database Count
Default Instance 42
Database Auto Shrink Policy 4
Data and Log File Loc'n Policy 0
FreeSpace_Threshold Policy 0

"Database Auto Shrink" is set to run against all "Online User Databases" and "Data and Log File Location" is set to run against Every Database.  Any hints on how to figure out why the policies are skipping over so many of my databases?

Ken

Nov 22, 2011 at 7:26 PM

I tried running EPM_EnterpriseEvaluation_3.0.0.ps1 at the powershell prompt and received several errors - odd because it worked last week.  In any event, I investigated the errors and managed to get them resolved by first running Initialize-SqlpsEnvironment.ps1 (found here: http://blogs.msdn.com/b/mwories/archive/2008/06/14/sql2008_5f00_powershell.aspx).  The good news is that everything works using this method.

Source Database Count
Default Instance 42
Database Auto Shrink Policy 42
Data and Log File Loc'n Policy 42
FreeSpace_Threshold Policy 51

I tried adding Initialize-SqlpsEnvironment.ps1 to the Agent job but that caused it to fail.  I appear to be cursed with some sort of security configuration problem that is probably causing all my issues.  The error message is "The term 'Add-PSSnapin' is not recognized as the name of a cmdlet, function, script file, or operable program.

Coordinator
Nov 26, 2011 at 1:17 AM

Hello Ken,  Try addressing the security per the other thread to see if your issues are resolved.  Thanks!

Coordinator
Dec 3, 2011 at 1:30 AM

Ken, out of curiosity, what is your PowerShell script execution level set at? To check, open a PowerShell window (as adminstrator) and type Get-ExecutionPolicy and press enter. If it doesn't say RemoteSigned then type 'Set-Execution Policy RemoteSigned' (don't type the single quotes).

Dec 5, 2011 at 4:22 PM

"Get-ExecutionPolicy" returns "RemoteSigned".

Coordinator
Dec 5, 2011 at 7:26 PM

How come you're putting Initialize-SQLpsEnvironment.ps1 in agent job? The only script that needs to be automated is EPM_EnterpriseEvaluation_3.0.0.ps1