Recently after one of my presentations a Twitter-buddy of mine, Argenis Fernandez ( Blog | Twitter ), showed me something interesting. By default when you create a new policy the against targets default says ‘Every Database’ (see screenshot below).
Well this is a tad misleading as it apparently means “every” in the sense that it will apply this policy against every USER database. This means if you want to ensure autoshrink is not enabled on your system database (master, msdb, model, tempdb), for example, applying this policy with this default target setting won’t work. So how do we create a new condition/target that allows us to run a policy against both user AND system databases? We’re going to create a new custom condition that allows us to look at both types of targets.
First click on the down arrow next to Every and go down to the bottom of the list and select ‘New condition…’
First you want to give this condition a new name. For this demo we’ll call it ‘Every database – User and System’. The next step is choosing the Facet as well as the property we want to look at. For this particular condition we want to use the ‘Database’ facet (which is the default facet selected). Next click on the box under Field and click the down arrow to view all of the properties available under that facet.
The field we want to select for this is the @IsSystemObject property. Once you select it, make sure your Operator value is set to ‘=’, then click on the field under Value. Once again, when you click on the field and click on the arrow you are given pre-set options. For this value select ‘True’.
Now that you’ve selected your first clause that states to apply to system object, we will create a second clause that applies to user objects as well. To start click on the next line where it says ‘Click here to add a clause’. When you click on it you’ll notice you get a new row to be able to create a new expression. For the first column (AndOr) we want to select ‘OR’. This is important because if you select AND in this instance, you’ll never return a valid entry since an object is either a system object OR a user object, never both. Next select the @IsSystemObject again from Field value and finally set the value to FALSE. Your Expression should now look like this:
Click OK and you should now see your new ‘Every database – User and System’ in the Against Targets box. Click OK to close your policy. To test it apply your policy against your database via your Central Management Server (CMS). To do that drill down to your server, right-click the database click ‘Evaluate Policies…’
From the Evaluate Policies box click on the ellipses box to select your Source. Your source is where your policies are located. With PBM you can either select individual policies (XML file) or you can select a SQL 2008 Server that has the policy you want. In my case I am selecting a server that contains the policies so all of the policies on that server will appear in my list. Select the policy we modified with the new target.
To verify we’re using the right target click on the policy name (in this case ‘Database Auto Shrink’). This will open the policy itself and you can verify that the correct target is in place. You should see your ‘Every database – User and System’ in the Targets window. Click Close to close the policy. With your policy checked click on the Evaluate button to proceed. The policy will then run and show you your results. In the target details box you should see your policy has run against all your databases, both user and system.
There is also a way to create this through a script. I created this particular via the SSMS gui but if you want to export it you can let SSMS create the T-SQL Script for you and share the code. To do this right-click on the condition in SSMS, Script Condition as, CREATE TO. To have it go directly to creating the .SQL script file for you select ‘File…’ otherwise lets select the ‘New Query Editor Window’ so we can see our result.
For this particular policy you should see something similar to this be output to SSMS:
Declare @condition_id int EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'System and User', @description=N'Allows you to select both user databases as well as all system databases.', @facet=N'Database', @expression=N'<Operator> <TypeClass>Bool</TypeClass> <OpType>OR</OpType> <Count>2</Count> <Operator> <TypeClass>Bool</TypeClass> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <TypeClass>Bool</TypeClass> <Name>IsSystemObject</Name> </Attribute> <Function> <TypeClass>Bool</TypeClass> <FunctionType>True</FunctionType> <ReturnType>Bool</ReturnType> <Count>0</Count> </Function> </Operator> <Operator> <TypeClass>Bool</TypeClass> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <TypeClass>Bool</TypeClass> <Name>IsSystemObject</Name> </Attribute> <Function> <TypeClass>Bool</TypeClass> <FunctionType>False</FunctionType> <ReturnType>Bool</ReturnType> <Count>0</Count> </Function> </Operator> </Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT Select @condition_id GO
And that’s it! You now have your T-SQL Code to create your new condition on other SQL 2008 Servers. Note of caution, however, be careful about how you use this as messing with system databases can be disasterous if you’re not careful.
