Policy Based Management to find number of SQL data and log files

By: Svetlana Golovko | Comments (6) | Related: > Policy Based Management

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..."
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"
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"

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"

Create Policies

In SSMS go to the Management > Policy Management > Policies
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.
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:

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:

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.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

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