Introduction
In this article I’m walking you through how to setup data file free space monitoring using SQL Server 2008 Policy-Based Management feature. You may think “Hey dude this is not a monitoring tool…!” but my answer would be “We can make this feature monitor data files free space on a database or instance”. Initially I wondered about introducing Policy-Based Management in SQL Server 2008. I know little bit about using policy management in Windows to enforce security policies and I felt that it is little complicated. But when I started working on Policy-Based Management in SQL Server 2008, I found it is really a cool and interesting feature which provides lots of options and flexibility to enforce your required policies in a database environment.
PBM (Policy-Based Management) in SQL Server 2008 allows a DBA to manage SQL Server instances by intent through clearly defined policies. These policies allow the DBA to specify rules for which objects and their properties are created, or modified, which can reduce potential administrative errors. Policies can be applied on a single database, a single SQL Server instance or on all the SQL Servers that you manage including SQL Server 2005 and 2000 instances. The policy-based framework implements the policies behind the scene with a Policy Engine, SQL Server Agent jobs, SQLCLR, DDL triggers and Service Broker.
The Policy-Based Management feature is exists in SQL Server Management Studio (SSMS). In the object explorer expand the Management node and then Policy Management; you can see the Policies, Conditions and Facets nodes (See below pic).
If you expand the Facets node, you can see a list of pre-defined facets (see below pic).
Double click on the facets or right click and select properties of a facet, you can see a collection of predefined properties. These properties are used to define a condition (see below pic). For Example: @IsReadOnly=False
Before you start working on policies, you should know the terms and execution modes of the policy-based management.
Terms
- Target – an entity that is managed by Policy-Based management; e.g. a database, a data file, a table, an index, etc.
- Facet – a predefined set of properties or characteristics of a target that can be managed
- Condition – a property expression that evaluates to True or False; i.e. the state of a Facet
- Policy – a condition to be checked and/or enforced against a target.
- Category – a set of policies
Execution Modes
A policy can be executed manually or automatically. There are four possible execution modes as listed below.
- On Demand. This mode evaluates the policy only on demand of the user.
- On Change - Prevent. This automated mode uses DDL triggers to enforce the compliance.
- On Change - Log Only. This automated mode uses event notification to evaluate a policy when a relevant change occurs and logs policy violations.
- On Schedule. This automated mode uses a SQL Server Agent job to run periodically to evaluate a policy.
All the above modes will log the policy violation detail in msdb database. Out of the four execution modes, the “On Change – Prevent” and “On Change – Log Only” modes are more helpful to the DBA to catch or prevent incompliance in real time.
Creating Policy and Conditions
OK, now I’m going to walk through you, how to create policy and conditions in SSMS. The requirement is if any data file(s) free space falls below 10% free space, this event needs to be captured. Policies and conditions can be created in any order, it is not necessary to create them in a sequence way. So I’m going to create the conditions prior to policies.
To fulfill our requirement we need two conditions, one is “DataFile_FreeSpace_Condition” to validate the data file free space and other is “Filter_UserDatabases_Condition” to filter only user databases.
DataFile_FreeSpace_Condition
The required condition is to validate that the data file current free space should be >= 10% . This condition will fail if the data file(s) available free space is less than 10%.
In SSMS, navigate to Management, Policy Management and right click on Conditions then select the New Condition (see below pic).
Type “DataFile_FreeSpace_Condition” in the condition Name column and select the “Data File” facet from the Facet drop down box (see below pic).
In the Expression table Click on the Advanced Edit button for the Field column. In the Advanced Edit window, select the Subtract() function from Functions and Properties list box, the function needs two Numeric Expressions (see below pic).
From the same Functions and Properties list box, select the first expression as @size (Data file size in KB) and second as @UsedSpace (Data file used space in KB) (see below pic) and Click the OK button. When the server evaluates the policy, this expression result will give the actual available free space on the data file.
In the Expression table select >= in the Operator column drop down box then Click Advanced Edit button of the value column.
In the advanced edit window, from the Functions and Properties list box select the Multiply() function, which also requires two numeric expressions, select @Size as the first expression and select Divide() as the second. Enter 10 and 100 as its first and second expressions then Click the OK button and the OK button again to Save the Condition (see below pic). When the server evaluates the policy, this expression result will give the 10% threshold value of the data file. (For Example: Size=100 KB then the 10% free space is 10 KB)
Filter_UserDatabases_Condition
The requirement is that the policy needs to evaluate only on the user databases. As above, select the new condition and type “Filter_UserDatabases_Condition” in the condition Name column then select the Database facet.
In the expression table select the @ID from the Field column then select > from operator column and enter 4 in the value column then click OK button and OK button to save the condition (see below pic). The default database ids between 1 to 4 are system databases. So this expression will filter all the user databases except system databases.
Creating the Policy
Click on the Policies node under Management and then Policy Management nodes in the SSMS. Type “DataFile_FreeSpace_Threshold_Policy” in the name column and navigate to the Check Condition drop down box, where you can find the available conditions and select the “DataFile_FreeSpace_Condition” (see below pic).
In the below picture you can see the available targets those are going to be evaluated by the selected check condition. Here the condition will evaluate every data files in every Filegroup in every Database.
To avoid evaluating the condition on the system databases, select the Filter_UserDatabases_Condition on the Every[S1] drop down box in Database target (see below pic)
Setting up Evaluation Mode
In the same policy window, below the Against targets box you can see the Evaluation Mode drop down box and select On Schedule option (see below pic).
On Schedule option will open up a new text box just below with Pick and New buttons (see below pic). Then Click New button to create a new schedule to evaluate the policy.
Schedule the data file free space check policy to evaluate every 1 hour then Click OK button and Click OK again to save the policy (see below pic).
As per this schedule, a new SQL job will be created and evaluate the policy. You can find the new job named as “syspolicy_check_schedule_9E6DED7F-F1E6-4F2F-A052-4786EBF47820” in the SQL Agent jobs list. (see below pic).
Evaluate the Policy
Now the Data File Free Space Check policy is ready to evaluate the data files of each database. You can evaluate the policy manually if needed. To do that, right click the policy and click Evaluate to evaluate the policy on all user databases (see below pic).
After you click this, you can see the targets being evaluated one by one in a new Evaluate Policies window (see below pic) and if any of the targets violate the policy they will be red tagged with the rest of the servers that did not violate the policy having a green tag.
In the Evaluate Policies window, expand the target column and see the target details. In the below picture, in the Target Details table, you can see the Northwind data file (selected row in the below pic) violated the Data file free space policy. The target column in this table will list the data file details in the following format.
SQL\Instance Name\ (Default or Named)\(Databases)\Database Name\ (FileGroups)\FileGroupName\Filename
For Example: SQLSERVER:\SQL\A-KRISJ1\DEFAULT\Databases\Northwind\FileGroups\PRIMARY\Files\Northwind
If you click on the data of the details column (on above pic), it will open a new Results Detailed View window as below, and you can see the expected data file free space in the Expected Value column and the current free space in the Actual Value column.
As per below pic, the Northwind data file is violated the free space policy because the Actual value (0 KB) should be >= the expected value (334.8 KB).
After setting up this policy I doubted, whether it shows correct data or not. I verified the actual available free space for Northwind database with the Database Shrink file option window.(see below pic) You can get this in SSMS by navigating to the Databases node and right clicking on the Northwind database. Then click Tasks, then Shrink, then Files.)
You can see the Actual Value 0.00Mn Available Free Space box (see below pic)
As I specified above, the policy execution history details will be stored in MSDB database. You can pull them out using the following system views.
- syspolicy_policies
- syspolicy_policy_execution_history
- syspolicy_policy_execution_history_details
The below sample query will help you to get the last 30 minutes detailed execution information about the DataFile_FreeSpace_Threshold_Policy. You can see the out of the query in the below pic.
SELECT SP.name,*FROM syspolicy_policy_execution_history_details PHD
INNER JOIN syspolicy_policy_execution_history PH
ON PH.history_id=PHD.history_id
INNER JOIN syspolicy_policies SP
ON SP.policy_id=PH.policy_id
WHERE PH.result=0
AND End_date>DATEADD("mi",-30,GETDATE())
AND SP.name='DataFile_FreeSpace_Threshold_Policy'
The column Target_query_expression will provide the data file names which are violated the data file free space policy. Based on the data files list, you can setup a SQL agent job to alert the DBA team to take further action on it.
The SQL Server instance logs the policy violation details in the SQL Error log and windows Application log as well.
Note:
The Result_Detail column (right most column in the above pic) is an xml type column which has expected and actual values in it (see Results Detailed view pic 3rd pic from bottom), if needed, you can parse the xml tags to take the values out to send with the alert notification.
Conclusion
Hope this article would have taught you how to setup a SQL Server policy to monitor if data file(s) free space falls below a specified threshold. In the same policy, if you select Log File facet instead Data File then the policy will evaluate the Log file free space. It’s a powerful DBA tool to administer your database environment.
References
http://msdn.microsoft.com/en-us/library/dd542632.aspx
http://technet.microsoft.com/en-us/library/bb510667.aspx
http://www.mssqltips.com/tip.asp?tip=1492
http://technet.microsoft.com/en-us/magazine/2008.04.overview.aspx
