Enable SQL Server Transaction Log Growth for All Databases


By:   |   Updated: 2010-11-03   |   Comments (2)   |   Related: More > Policy Based Management

Problem

There is an easy way to quickly enable transactional log 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 transactional log file growth for all the databases using Policy Based Management.

Solution

Database Administrators can use Policy Based Management (PBM) to quickly enable transactional log file growth for all 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 transaction log file growth to a satisfactory percentage to avoid the transaction log file from running out of space. 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.

Following are the steps to create the policy.

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

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

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

Enable Transactional Log File Growth for all the Databases

3. In Create New Condition window you need to provide the name of the condition as "Transactional Log File Growth for all the Databases" and then select the Facet "Log 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.

Under Expression select Field value as @GrowthType

  • 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" option under "Against targets" will be checked as shown in the below snippet. Click OK to create the policy.

 by default "Every Database" option 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 in the below snippet.

sql server 2008

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.

click a "View..." link under Details, and you will be able to see detailed results

7. When you click a "View..." link under Details, you will be able to see detailed results for the AdventureWorks database. The expected value for @Growth is 10 and @GrowthType was Percent. However, the actual value configured is None as Growth Type for the transaction log file of AdventureWorks database. Next step will be to apply the policy, so that all the transaction log files of all the databases are set to grow at 10 percent.

results detailed view

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.

Policy Evaluation Warning

9. Once the policy suggestion is applied you will be able to see the below screen that shows all databases are in compliance now. (Click on the image to see a bigger image) Click Close to exit.

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

Next Steps


Last Updated: 2010-11-03


get scripts

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





Comments For This Article




Wednesday, June 12, 2013 - 5:00:51 PM - Thomas Back To Top (25416)

Thanks for the tip a great time saver

 


Wednesday, November 03, 2010 - 8:09:52 AM - Rahul Back To Top (10328)

It is very important to have sufficient free space available in transaction log file across all production databases. By following this tip one can easily verify the transactional log space settings and take proactive steps. Once again thanks to have found a easy solution to a very common problem.

Once again a Great Tip from MSSQLTIPS, please continue your good work.

 



download


Recommended Reading

Why SYSPOLICY_PURGE_HISTORY job fails in SQL Server 2008 Failover Cluster Instance

Using Policy Based Management in SQL Server

Internal SQL Server Certificates have expired is this an issue

Enforce SQL Server Database Naming Conventions Using Policy Based Management

Configuring Alerts for SQL Server Policy Based Management





get free sql tips
agree to terms


Learn more about SQL Server tools