Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enable Data File Growth for all the Databases Using Policy Based Management


By:   |   Last Updated: 2010-10-15   |   Comments   |   Related Tips: More > Policy Based Management

Problem

There is an easy way to quickly enable data file growth for all the databases in SQL Server 2008. In this tip we cover the step by step process for setting up a policy to Enable Data File Growth for all user databases using Policy Based Management.

Solution

Database Administrators can use the Policy Based Management (PBM) to quickly enable data file growth for all user databases. You can follow the below steps to create and evaluate the policy. It's a best practice to make sure the DBA has enabled data file growth to a satisfactory percentage depending upon an anticipated growth of the database. If you are not familiar with Policy Based Management (PBM) then you can start with the following tip Using Policy-Based Management in SQL Server 2008.

1. Connect to a SQL Server 2008 instance using SQL Server 2008 Management Studio and expand Management -> Policy Management and then right click Policies and select New Policy... as shown below.

Enable Data File Growth for all user databases using Policy Based Management  in SQL Server 2008

2. In the Create New Policy window provide the name of the policy as Enable Data File Growth for all the Databases. In Check Condition you need to click the New Condition.... option to open up the Create New Condition window.

Connect to a SQL Server 2008 instance using SQL Server 2008 Management Studio

3. In Create New Condition window you need to provide the name of the condition as "Data File Growth for all the Databases" and then select the Facet "Data File". Under Expression select Field value as @GrowthType and choose Operator value as ' = ' and then value as Percent. Next add one more clause by selecting @Growth in Field and choose Operator value as ' = ' and then value as 10 as shown in the below snippet. Click OK to save the condition and to return to the parent Create New Policy window.

"Data File Growth for all the Databases" and then select the Facet "Data File"

  • Use @GrowthType to get or set the growth type for the data file in kilobytes or percent.
  • Use @Growth to get or set the growth increment for the data file in kilobytes or percent.

4. In Create New Policy, by default "Every Database" under "Against targets" will be checked as shown in the below snippet. Click OK to create the policy.

by default "Every Database" under

5. Once your policy is created, the next step will be to evaluate the policy. Right click the policy and select the Evaluate option from the drop down list as shown below.

the next step will be to evaluate the policy

6. The below snippet shows the result set returned by the policy. (Click on the image to see a bigger image) We can see that for most of the databases the policy has failed.

we can see that for most of the databases the policy has failed

7. When you click one of the "View..." links under Details, you will be able to see a detailed result for that database. The expected value for @Growth is 10 and @GrowthType was Percent. However, the actual value reflects 16384 KB of Growth for the data file for the AdventureWorks database. The next step will be to apply the policy, so that all the data files of all the databases are set to grow at 10 percent.

apply the policy so that all the data files of all the databases are set to grow at 10 percent

8. To enable the data file growth for all the databases select the target databases and click the Apply button. This action will pop up a Policy Evaluation Warning as shown in the below snippet. Click Yes to apply the policy.

 a Policy Evaluation Warning

9. Once the policy is applied you will be able to see the below screen that shows all databases are in compliance now. Click Close to exit.

 see the below screen that shows all databases are in compliance now

Next Steps


Last Updated: 2010-10-15


next webcast button


next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools