Policy-Based Management(Check AutoGrowth Of Database Files-Data File Facet)

Policy-Based Management is a feature that introduced with SQL Server 2008. It allows us to set the rules we want in our systems, to determine our standards and to perform our controls automatically. For example, we can create a policy for control purposes so that the names of our stored procedures do not start with a number or database recovery models are not simple.

To comprehend “Policy Based Management (PBM)”, you need to understand some concepts.

Facet: A feature that can be managed by the PBM.  There are two facets that we use in our example (Data File and Log File).

Using these facets, we can control the auto growth rates of database files. Auto Growth is very important for performance in the database. Before reading this article, I suggest you read the article “Vlf (Virtual Log File) Count“. In the article, you can find the reason and importance of the correct configuration of “auto growth”.

You can access all facets via SSMS as follows. You have to double-click on Facet details.

Condition: checks whether the sub properties of the related facets meet the specified condition.

For example, you can use the Growth and GrowthType sub-property of the Data File and Log File facets to control the growth size and the type of growth. In this way, you can detect and correct the remaining as percent.

By default I set the auto growth of data files in database to 512 MB, auto growth of log files to 256 MB. For Data File, let’s create a policy that identifies databases that are not in this way. You can create the same for the log file.

We click New Policy on the Management> Policy Management> Policies tab as follows.

We give a name to Policy and we need to create a condition from Check Condition.

We click on New Condition.

We give a name to the condition from the Name section.

We choose the Data File facet in the Facet section. If you will check log file, you must select Log File Facet.

By clicking “…” in the Expression section, we select @Growth and @GrowthType, which are the sub properties of the Data File facet.

We select “=” in Operator and write 524288 in Growth and KB in GrowthType.

Thus, if there is a data file that does not have 512 MB of auto growth, the policy will fail.

By selecting “On Schedule” from the Evaluation Mode section, we determine at what intervals the policy will be checked, and then click the Enable check box.

If we choose On Demand, we only do checks when we execute policy.

In the server restriction section, if you have a server-based condition, you can control your condition by creating a server-based condition.

If we want to automatically check at regular intervals, we click new in schedule and determine the frequency of the policy check. As you can see below, we set it to work once every day at 00:00:000.

We created our policy. To run manual, we click Evaluate as follows.

When I performed this operation in my local, I have seen that system databases do not comply with this rule. Because the system databases will not grow too much, you may not change their settings, but I think it would be better to change them.

When you refresh on Instance, a red color x mark appears next to Instance that looks like the letter. The reason for this is that there is an unsuccessful policy that SQL Server wants to notify us. When you see this mark, you must look at the defined policies and correct the problems.

There are many controls you can do on SQL Server with Policy Based Management. And I think a professional database administrator should use this feature that SQL Server provide us with all the details. You can see other controls you can do with Policy Based Management by typing “Policy Based Management” in our search section.