Policy Based Management has 4 evaluation modes and if you are not already familiar with them you can go HERE to get more information.  Today I want to talk about the “On Change: Prevent” mode and why you don’t always see it as an option.

The options you see available are dependent on the facets used in your check condition.  In order for the “On Change: Prevent” option to be available your condition must check something that raises a DDL event.

Let’s use Auto Shrink as an example and start by verifying if it raises a DDL event or not. We’ll use the old DDL trigger method to see what happens.  Here’s the code to create the trigger, and it just does a simple PRINT statement back to the screen if it fires:

CREATE TRIGGER trg_CatchShrink
ON ALL SERVER
FOR ALTER DATABASE
AS
BEGIN
PRINT ‘Auto shrink was changed and fired the trigger’
END

Now that we have our trigger setup let’s use the below code to enable Auto Shrink and see if it fires.  Make sure to change the database name to your TEST database.

USE [master]
GO
ALTER DATABASE [MyTestDB] SET AUTO_SHRINK ON WITH NO_WAIT
GO

If you’re following along and running the code then you will have seen the text of the PRINT command in your output.  We have now verified that enabling Auto Shrink on a database does indeed raise a DDL event.  So now your homework is to go create a policy that checks to make sure Auto Shrink is not enabled.  Are you done, yet?  That’s okay I’ll just give you the answer.  If you create a policy that checks the Auto Shrink setting then you will see that the “On Change: Prevent” option is NOT available.

So why is that option not available when we have proven that changing the Auto Shrink setting does raise a DDL event?  It’s because you can only use facets where ALL properties of that facet raise a DDL event.  That’s right.  Every single property in a facet has to raise a DDL event for the “On Change: Prevent” option to be available.  Now don’t get too excited because you know Auto Shrink is in more than one facet and you think you can just use another facet.  Surprise!  None of those facets support “On Change: Prevent” either, but good for you for knowing it was in more than one place.

So which facets support the “On Change: Prevent” evaluation method?  The following script will tell you:

SELECT name
FROM [msdb].[dbo].[syspolicy_management_facets]
WHERE execution_mode & 1 = 1

Let’s not forget to clean up after ourselves and remove our trigger as well as the Auto Shrink setting we changed.

DROP TRIGGER trg_CatchShrink
GO
ALTER DATABASE [MyTestDB] SET AUTO_SHRINK OFF WITH NO_WAIT
GO