Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Policy Based Management to check number of SQL Server data and log files


By:   |   Read Comments (6)   |   Related Tips: More > Policy Based Management


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


Problem

To meet our database configuration standards we want to make sure that we have only one database transaction log file and more than one data file. In this tip we look at how this can be done using a policy.

Solution

In this tip we will create two policies that can be used for checking the number of data files and the number of transaction log files. We will create custom conditions to do this.  You can read more about custom conditions in this tip.

The reason why I have set this policy up is that by having multiple transaction log files it does not give any performance advantage and there are very rare cases when you need more than one log file (read this post to find out why). Also, having more than one data file (other than the default filegroup) can provide advantages of putting large tables or indexes on different disk arrays and separating database I/O. Refer to this tip to learn more about different scenarios when designing a filegroup configuration.

In this tip we will create three conditions and two policies.

Create Condition to Check for Number of Log Files

Create the condition that will be used to check the number of log files:

  • In SQL Server Management Studio (SSMS) go to the Management > Policy Management > Conditions
  • Right click "Conditions", then click "New Condition..."
     
    Management->Policy Management->Conditions
  • Enter the name for the condition: "_Demo_cond: Number of Log files"
  • Select "Database" facet
  • Enter the following expression as the "Field"
     
    ExecuteSql('Numeric', 'SELECT COUNT(file_id) FROM sys.database_files WHERE [type] = 1') 

    Note:
    • "Type = 1" in the query above is "LOG" (for the log files).
  • Add the expression to check if the value is equal to "1"

    In SQL Server Management Studio (SSMS)
  • Click "OK"

Create Condition to Check for Number of Data Files

Similar to the condition above create the condition that will be used to check the number of data files:

  • In SSMS go to the Management > Policy Management > Conditions
  • Right click "Conditions", then click "New Condition..."
  • Enter the name for the condition: "_Demo_cond: Number of Data files"
  • Select "Database" facet
  • Enter the following expression as the "Field"
     
    ExecuteSql('Numeric', 'SELECT COUNT(file_id) FROM sys.database_files 
    WHERE [type] = 0 AND data_space_id NOT IN 
     (SELECT i.data_space_id 
     FROM sys.filegroups fg JOIN sys.fulltext_indexes i 
     ON fg.data_space_id = i.data_space_id )')

    Note:
    • "Type = 0" in the query above is "ROWS" (for the data files). This excludes FILESTREAM data files.
    • The query also checks that the files that contain full-text filegroups are excluded from this check (not counted).
  • Add the expression to check if the value is greater than "1"

    Add the expression to check if the value is greater than "1"

Create Condition to Check Only User Databases

Now create a condition that will be used as a target to check only users databases.

  • In SSMS go to the Management > Policy Management > Conditions
  • Right click "Conditions", then click "New Condition..."
  • Enter the name for the condition: "_Demo_cond: Non-system DB"
  • Select "Database" facet
  • Select @IsSystemObject as the "Field"
  • Add the expression to check if the value is equal to "False"

    Add the expression to check if the value is equal to "False"

Create Policies

  • In SSMS go to the Management > Policy Management > Policies
  • Right click "Policies", then click "New Policy..."
     
    Right click "Policies", then click "New Policy..."

  • Enter the name for the policy: "_Demo_pol: Check Number of Transaction Log Files"
  • Select condition "_Demo_cond: Number of Log files" under "Check Condition"
  • Replace value in "Against targets" field from "Every Database" to the new condition that was created to check only users databases.

    Enter the name for the policy: "_Demo_pol: Check Number of Transaction Log Files"
  • Click "OK" to save the policy.
  • Similar to the first policy create the second policy named "_Demo_pol: Check Number of Database files" using "_Demo_cond: Number of Data files" condition:

    "_Demo_pol: Check Number of Database files" using "_Demo_cond: Number of Data files"

Evaluate the Policies

There are several ways to evaluate the policies:

  • You can use Registered Servers in SQL Server Management Studio (SSMS) to evaluate the policy. Refer to this tip for more information.
  • Policies could be evaluated using the Central Management Server as well. Refer to this tip for the details.
  • Another option is to schedule policies evaluation by setting the Execution Mode to "On Schedule". Refer to this tip for an example.
  • There is also an option to evaluate policies using PowerShell and schedule it as a job as well.

Evaluate a single server or multiple servers and review the results:

Policy evaluated
Next Steps
  • Extend the query that checks the number of data files to check that all files are not in the DEFAULT filegroup and/or that files are on different disks.
  • Learn more about different scenarios when designing a filegroup configuration in this tip.
  • Make sure that your applications supports multiple database files (yes, we had one of the applications that supported only one database file).
  • If you started using multiple files and filegroups make sure that you reviewed your backup/restore strategy. Read here about benefits and disadvantages of file backups.
  • Read more tips on Policy Based Management.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 06, 2013 - 8:05:51 AM - Svetlana Golovko Back To Top

Thank you for reading and for you comments, Igor and Suzanne.


Monday, June 03, 2013 - 8:11:26 AM - Suzanne Back To Top

Great stuff. Very useful. Thanks for posting!


Tuesday, April 30, 2013 - 4:42:12 AM - Igor Micev Back To Top

A good article on how to use policy based management.

Thank you


Monday, April 29, 2013 - 10:12:38 PM - Svetlana Golovko Back To Top

I agree with Sreekanth.

You need to find the root cause why the database is performing badly, find out where the problem is (memory, CPU, waits, blocking etc).

If you are conserned just about the database's size you can contact your third party vendor and check if they have any data archival solutions.


Monday, April 29, 2013 - 5:33:05 PM - sreekanth bandarla Back To Top

Hi Jignesh - Database Size never degrades the Database Performance, provided you are providing what SQL Server needs to serve the purpose and configure database properly. SQL Server is designed to handle tera bytes worth of data with ease, provided it is configured and maintained properly and Of course the SQL Code is the key player:)

 


Monday, April 29, 2013 - 10:14:59 AM - Jignesh Back To Top

Hello,

We have third party software and the size of the database is too big and hence it degrades the performannce. Is there any way we can improve the performance ?


Learn more about SQL Server tools