Querying Database File Properties

By | In Blog, SQL Server | July 28th, 2015

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?

sql_prop

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:

Here’s the output:

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

Contact Us
JP Chen
Practice Leader of SQL Server Services
JP has over 13 years of experience in the IT industry specializing in web development, data analysis, and database administration. He has worked on supporting enterprise level corporations in the financial, retailing, pharmaceutical, aerospace, fashion, and education industries as a DBA and Tech Lead. He has initiated, designed, and developed comprehensive solutions on database documentation, monitoring and alerting, stabilization, and continuous improvements with performance monitoring and tuning. He enjoys sharing his new findings, knowledge, and practical hands-on experiences on SQL Server via his two blogs: www.handsonsqlserver.com and www.handsontsql.com.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).