Recently after one of my presentations a Twitter-buddy of mine, Argenis Fernandez ( | ), showed me something interesting. By default when you create a new policy the against targets default says 鈥楨very Database鈥� (see screenshot below).
Well this is a tad misleading as it apparently means 鈥渆very鈥� 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鈥檛 work. So how do we create a new condition/target that allows us to run a policy against both user AND system databases? We鈥檙e 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 鈥楴ew condition鈥︹��
First you want to give this condition a new name. For this demo we鈥檒l call it 鈥楨very 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 鈥楧atabase鈥� 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 鈥楾rue鈥�.
Now that you鈥檝e 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 鈥楥lick here to add a clause鈥�. When you click on it you鈥檒l notice you get a new row to be able to create a new expression. For the first column (AndOr) we want to select 鈥極R鈥�. This is important because if you select AND in this instance, you鈥檒l 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 鈥楨very 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 鈥楨valuate 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鈥檙e using the right target click on the policy name (in this case 鈥楧atabase Auto Shrink鈥�). This will open the policy itself and you can verify that the correct target is in place. You should see your 鈥楨very 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 鈥楩ile鈥︹�� otherwise lets select the 鈥楴ew 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鈥檚 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.