Querying Database File Properties

You may need to find out the filegroups that are in a database, the logical file name, initial size, autogrowth/maxsize, path, and file name so that you can determine if files in a filegroup exist on separate hard drives with free disk space and if the files are not limited in size. In addition, you may wish to increase the file autogrowth size if they are set at rather small increments or set to increase by a percentage.

You can right-click on the database and select “Properties” in the pop-up menu,then click on the “Files” page. You will be able to view the properties of the database files as shown in the following screen-shot. But, what if you need to see all the databases that are in the SQL Server instance and you need to save the database file properties for all databases in an Excel file?

Can this be done? Yes, and you can do it too. The following are the steps for implementation:

1. 1. You’ll need to create a skeleton temporary table to store the results for each of the database properties.
2. 2. Use sp_MSForEachDB stored proc to load the database properties into the temporary table.
3. 3. Query the temporary table.

Here are the system views, functions, conversions, formulas, and case expressions that you need to know to get started:

• SYS.DATABASE_FILES: Contains one row per file for a database. It is per-database view. We will need to loop through all the databases in the SQL Server instance to get all the database file properties for all databases.
• SYS.FILEGROUPS: Contains one row for each data space that is a filegroup. It inherits a list of columns from the sys.data_spaces system view. We will query this view to retrieve the filegroup names.
• DB_NAME(): This function will return the database name.
• STR(): Returns character data converted from numeric data. We will need to concatenate the numeric data with characters after converting the database file growth value from pages to MB.
• LTRIM(): Removes the leading blank spaces and return the characters.
• Converting Pages to MB: The size and max_size columns in the SYS.DATABASE_FILES system table are stored in 8-KB pages. To convert pages to MB, you will need to divide it by 128.
• CAST(): Converts an express of one data type to another. We will use the CAST() function to convert the file size to FLOAT data type and then round it to 2 decimal places.
• FILEPROPERTY(): Returns the specified file name property value. We will use this function to find the space used for the files.
• ROUND(): Returns a numeric value, rounded to the specified length or precision.
• CASE EXPRESSION: Evaluates a list of conditions and returns one of the possible result expressions. In this case, we will use the search CASE expression.

Here’s the script that will do the magic described:

-- Querying the database file properties
-- ****************************************************************************
-- Copyright © 2015 by JP Chen of DatAvail Corporation
-- This script is free for non-commercial purposes with no warranties. 
-- ****************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT DB_NAME() AS DatabaseName,
df.name AS [Logical File Name],
df.type_desc AS [File Type],
CASE 
	WHEN fg.name IS NULL THEN 'LOG'
	ELSE fg.name
END AS Filegroup,	
CASE 
    WHEN is_percent_growth = 0 
        THEN LTRIM(STR(df.growth/128,12,1)) + ' MB, ' 
    ELSE
        'By ' + CAST(df.growth AS VARCHAR) + ' percent, '
END + 
CASE 
    WHEN df.max_size = -1 THEN 'Unlimited'
    ELSE 'Limited to ' + LTRIM(STR(df.max_size/128, 10, 1)) + ' MB'
END AS [Autogrow / Max Size],
ROUND(CAST((df.size) AS FLOAT)/128,2) AS Reserved_MB,
ROUND(CAST((FILEPROPERTY(df.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
ROUND((CAST((df.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(df.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB,
df.physical_name AS [Physical File Name]
INTO #DBFileProperties
FROM sys.database_files df LEFT JOIN sys.filegroups fg
	ON df.data_space_id = fg.data_space_id
WHERE 1 = 2

EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #DBFileProperties
SELECT DB_NAME() AS DatabaseName,
df.name AS [Logical File Name],
df.type_desc AS [File Type],
CASE 
	WHEN fg.name IS NULL THEN ''LOG''
	ELSE fg.name
END AS Filegroup,	
CASE 
    WHEN is_percent_growth = 0 
        THEN LTRIM(STR(df.growth/128,12,1)) + '' MB, '' 
    ELSE
        ''By '' + CAST(df.growth AS VARCHAR) + '' percent, ''
END + 
CASE 
    WHEN df.max_size = -1 THEN ''Unlimited''
    ELSE ''Limited to '' + LTRIM(STR(df.max_size/128, 10, 1)) + '' MB''
END AS [Autogrow / Max Size],
ROUND(CAST((df.size) AS FLOAT)/128,2) AS Reserved_MB,
ROUND(CAST((FILEPROPERTY(df.name,''SpaceUsed'')) AS FLOAT)/128,2) AS Used_MB,
ROUND((CAST((df.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(df.name,''SpaceUsed''))AS FLOAT)/128),2) AS Free_MB,
df.physical_name AS [Physical File Name]
FROM sys.database_files df LEFT JOIN sys.filegroups fg
	ON df.data_space_id = fg.data_space_id'

SELECT *
FROM #DBFileProperties

Here’s the output:

You may have noticed that this script has the “Used_MB” and “Free_MB” data columns that we had hoped the database properties page will eventually enable us to see. Now, you don’t have to wait any longer. You can even add in an extra column to show the percentage of usage. You can further customize this script to periodically capture the file properties and store them in a permanent table to keep track of file growth or simply for auditing purposes.