Creating Objects on a Specific Filegroup with Policy Based Management

SQL Server

At PASS Summit last week, I presented “The What, Why, And How of Filegroups” to a packed room. It was great to see so many people eager to learn about something that is fundamental to every SQL Server database, but often not understood well enough.

As always, during and after the session, there were a lot of questions. One really piqued my interest: “When creating a table or index, is there a policy in Policy-Based Management to force a query to specify the filegroup instead of using the default?”

Let’s find out!

I’m going to use my DBA database on my development instance, DBAInfo. The database currently has one filegroup, PRIMARY.

Next, I’ll add another filegroup and file, and make it the default.

I double-check my filegroups.

At this point, any table added without an “ON FilegroupName” clause should be created on the default. Let’s check.

I run my “What filegroup is that on?” query:

It has been created on the default, PBMTest, as expected.

Now, I’ll add a third, non-default filegroup, PBMTest2.

I’ll check my filegroups again.

Next, I want to create a policy that says “When a new table is created, put it on PBMTest2, even if that’s not the default.” In SSMS, I expand Management > Policy Management and right-click Policies > New Policy.

I give the policy a name, “CreateTableOnFilegroup”, and then click next to “CheckCondition” to set a condition. I name it “OnFilegroup”. The Facet is Table. I set the Field to @Filegroup, the Operator to “=”, and the Value to ‘PBMTest2’.

It’s set to every table in every database. I only want it to apply to my DBAInfo database, so I select Every > New Condition.

I give the Name DatabaseDBAInfo. I set the Facet to Database, the Field to @Name, the Operator to =, and the Value to ‘DBAInfo’. When I click OK, I’m back at the Create New Policy screen. I want to set Evaluation Mode to On demand and Server restriction to None. I click OK.

The one policy and two conditions are created.

Now, I go back to the query window and create another table, without specifying a filegroup to place it on.

What I want this to be created on PBMTest2. What filegroup is it on?

It’s created on PBMTest.

What? Why?

This comes down to the Evaluation Mode setting of my policy. There are a total of four modes available in PBM – On Demand, On Schedule, On Change – Log only, and On Change – Prevent. What we are looking to do here is enforce On Change – Prevent – if the table isn’t going to be created on the filegroup we specify, we want it to fail. Unfortunately, not all policies can use this mode.

I had to do some research to figure this out. First, read this blog post on Facets by the PBM team. At the bottom, there is a table showing the facets and what modes are supported. Then, read this further explanation of the types by Lara Rubbelke, Policy Evaluation on a Single SQL Server 2008 Instance. Lastly, there is this blog, Policy Evaluation Modes,by the PBM team.

For On Change – Log and On Change – Prevent to exist for a facet, there must be a DDL event triggered by it. This is not the case with every operation in SQL Server. The CREATE TABLE commands in one such example. Because of this, I cannot create a policy to check what filegroup a table is being created on before it is created.

In this case, I can still create the policy and check it either on a schedule or on demand. It will then tell me which tables were not created on that filegroup.

Thank you Simon for the great question!

Previous Post
What DBAs Need to Know About Hardware
Next Post
Introducing the SQL Server Plan Cache (and a Better sp_Blitz®)

3 Comments. Leave new

  • This is a great posting. Thanks for all the great information. This was a very interesting read. Also on another note. Are you using Gedit for your queries or did you just get SSMS to look like Gedit. It looks great. Please let me know how you did that.



  • I have had a post in my drafts for like 6 months on this and just now got around to finishing and posting it. It’s just a quick proof of concept using an example of how On Change: Prevent is not always an option for something you know raises a DDL event and why that is. I also show a query for how to find which facets support it.

    I am, however, going to go play with that WordPress plugin you mentioned. I have needed something like that for quite some time. Thanks for that tip!


Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.