Policy-Based Management is a feature that is introduced with SQL Server 2008. It allows us to set the rules we want in our systems, to set our standards and to make 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.
Before you read this article, I recommend that you read “What is SQL Server Transaction Log” to understand the concept of Transaction Log. You can use the Search section of our website for more information.
If the Transaction Log is not backed up and the recovery model of the database is not simple, the transaction log will continue to grow and the application will be unable to run because there will be no space on the disk for a while. Therefore, we need to check that Transaction Log is backed up. We can do this with the Policy Based Management system.
You may want to read the article “What is Database Recovery Model” about Recovery Model.
To comprehend Policy Based Management (PBM), you need to understand some concepts.
Facet
A feature that can be managed by PBM. For example, there is a facet called Database. And you can use this facet to create a policy that checks whether the transaction log is backed up.
You can access all facets via SSMS as follows. To access the facet details, you must double-click on it.
Condition
Checks whether the sub-property of the related facets provide the specified condition.
Let’s create a policy that checks the databases where transaction log file has not been backed up for the last 1 day if it has not simple recovery model.
Create a Policy
Right click Policies tab as follows from the Management> Policy tab and click New Policy.
We give a name to Policy on the screen that appears. We need to create a condition from Check Condition.
We’re clicking on New Condition.
We give a name to the condition from the screen that appears.
We select Database facet from Facet section.
By clicking… in the Expression section, we select @LastLogBackupDate, which is the subfeature of Database facet.
By selecting> from the Operator section, we write DateAdd(‘hour’, -24, GETDATE()) to the value. To write a date to the value section manually you must click … and open Advanced Edit.
Thus, if there is a transaction log that has not backed up for the last day, the policy will fail.
By selecting On Schedule from the Evaluation Mode, we select the interval at which the policy will be checked and then click the Enable check box.
If we choose On Demand, it only checks when we execute policy.
If you have a server-based condition, you can create a server-based condition from the server restriction section.
If you want it to be checked automatically at certain intervals, click New in the Schedule section and determine the frequency that the policy will check the condition. We set it up to work once every day at 12: 00: 000 PM.
When we create our Policy in this way, it will perform the necessary checks for all databases. But actually our goal is to do this check in databases that has not simple recovery model.
So we click on Every section and create a new condition.
We make the following selections on the screen that appears. Thus, it will only check databases that has not simple recovery model.
After doing this process, a screen like the following should come up.
We created our policy. We click Evaluate as below to run it manually.
I got a result as follows in my local. As you can see, policy has failed for TestDB. Because the recovery model of the TestDB database is Full and the Transaction Log file has not been backed up since the one day.
When we click on View, we can see why the policy is failed. As you can see, the trasanction log file of the TestDB database was never backed up.
When you refresh the instance, a red color x sign appears on the left side of the instance, that looks like the letter. The reason for this is that we have a policy that is failed on the instance. When you see this mark, you must look at the defined policies and fulfill their requirements.
With Policy Based Management, you can check many things on SQL Server. And I think a professional database administrator should use this feature that SQL Server offers us. You can find more articles on Policy Based Management at the following links.
“How To Check the Compatibility Level of Databases Using Policy Based Management”,
“How To Check Recovery Model of All Databases Using Policy Based Management”,
“How To Check Stored Procedure Names Using Policy Based Management”,
“How To Check VLF Counts in the Databases Using Policy Based Management”,
“How To Check SQL Logins Without Password Policy Enforced or Password Expiration Enabled Using Policy Based Management”,
“How To Check Disabled Audits Using Policy Based Management”,
“How To Check Auto Shrink Enabled Databases Using Policy Based Management”,
“How To Check Whether Availability Groups is Ready To Failover Using Policy Based Management”,
“How To Check Availability Group’s Backup Preference Using Policy Based Management”,
“How To Check Availability Group Automatic Failover Settings Using Policy Based Management”,
“How To Check Whether Readable Secondary is Enabled on Availability Groups Using Policy Based Management”,
“How To Check Whether Data File Sizes is Reached a Specific Size Using Policy Based Management”,
“How To Check Auto Close Option of Databases Using Policy Based Management”,
“How To Check Last Log Backup Time Using Policy Based Management”,
“How To Check Page Verify Option of Databases Using Policy Based Management”
