Select Page

How to Use sys.master_files For SQL Servers

Author: Eric Russo | | December 2, 2014

When using SQL Server, you may want to find file information to gather statistics you can use to make database management decisions.

This is when you may wish to use sys.master_files. This single, system-wide view contains a row for each file of a database as it is stored within the master database. It holds file information for all the databases in a SQL Server instance.

Also known as a system catalog view, sys.masterfiles can be used in lieu of sp_helpfile and actually returns more information.

sys.master_files is an offshoot of master database tables, which were physical tables in a pre-SQL 2005 master database that could be accessed and manipulated. It is now available in SQL Server 2008 through the most current version of SQL Server.

Those master database tables are now protected and available for viewing only as an object of the resource database.

As consultant Tim Ford explains in a piece on SQL Server Pro:

As an enterprise database administrator (DBA), I find myself wanting to look up file information on a regular basis for multiple databases at a time. I may need this information when ascertaining which files are consuming the most space on a particular volume or in looking for databases where files are set to auto-grow by a percentage or some unfortunately small increment.

A user has several options available when using sys.master_files. He/She can select all the server’s database files, but can also view specific files based on their size or physical locations. This might, for example, be used to return all the database files on a physical drive.

The results returned can also be sorted and ordered by file name. The view contains other information, such as the database file’s state and size—including the maximum file size—and whether it is a log or a data file.

Ford suggests using the following simple SQL code when using sys.master_files rather than relying on SELECT *:

SELECT DB_NAME(database_id) AS database_name
        , database_id
        , [file_id]
    , type_desc
    , data_space_id
    , name AS logical_file_name
    , physical_name
        , (SIZE*8/1024) AS size_mb
        , CASE max_size
                WHEN -1 THEN 'unlimited'
                ELSE CAST((CAST (max_size AS BIGINT)) * 8 / 1024 AS VARCHAR(10))
        END AS max_size_mb
    , CASE is_percent_growth
                WHEN 1 THEN CAST(growth AS VARCHAR(3)) + ' %'
                WHEN 0 THEN CAST(growth*8/1024 AS VARCHAR(10)) + ' mb'
        END AS growth_increment
    , is_percent_growth
FROM sys.master_files
ORDER BY 1, type_desc DESC, [file_id];

What the code above creates is a basic query containing the database name and ID. It also shows the file’s logical and physical name, the file type, the data_space_id, and also the file’s size and growth settings.

From this point, the results can be filtered and manipulated. This is useful for easily discovering those files using auto growth settings, among other things. It eliminates the need to use stored procedures and enables you to access the information without needing more coding.

Microsoft also notes:

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.master_files immediately after dropping or truncating a large object may not reflect the actual disk space available.

If you need help with this or other SQL Server issues beyond the scope of this post, please contact Datavail for more information on how our expert SQL Server DBAs can best support you and your organization with custom solutions tailored to your specific SQL Server needs.

Image: koya79/123RF.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

Scripting Out SQL Server Logins, Server Role Assignments, and Server Permissions

This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.

JP Chen | October 1, 2015

Using Nulls in DB2

If a column “value” can be null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known.

Craig Mullins | April 6, 2015

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.