To find which files belong to which SQL database, query the sys.master_files and sys.databases system catalogue views in SQL Server. A single JOIN across these two views returns every data and log file on the instance, along with the database name, logical file name, physical path on disk, and file size. This is one of the most practical diagnostic queries a DBA can run, and it takes seconds.

Why This Matters in the Real World

Here is a scenario that plays out more often than it should. A developer or IT manager restores a production database into a dev or test environment. No drama there. But production databases almost always run under the full recovery model, and that recovery model follows the database into the new environment. Without a log backup job configured in dev, the transaction log starts growing unchecked. In some cases, a single log file can consume tens of gigabytes overnight.

The next morning, a low disk space alert fires. The DBA logs in, finds a bloated log file, and then hits the second problem: the file name gives nothing away. Developers restoring databases rarely rename the underlying files to match any naming convention. You might be staring at a file called PROD_DB_log.ldf sitting in a dev environment, or worse, something completely generic like database2_log.ldf with no obvious owner.

This is exactly where querying which files belong to which SQL database saves time and removes guesswork.

The Query: Mapping SQL Server Database Files to Their Databases

The script below joins sys.master_files to sys.databases on database_id. It returns every file registered to the SQL Server instance, ordered by file size descending so the largest offenders appear first.

SELECT
    d.name          AS DatabaseName,
    m.name          AS LogicalName,
    m.physical_name AS PhysicalName,
    m.size          AS FileSizeIn8KBPages
FROM sys.master_files m
INNER JOIN sys.databases d
    ON m.database_id = d.database_id
ORDER BY m.size DESC;

Run this against any SQL Server instance and you get a complete picture of every data file (.mdf, .ndf) and log file (.ldf) attached to that server, mapped back to its owning database.

Understanding the Output

A few things worth knowing about what this query returns:

  • DatabaseName is the logical name of the database as it appears in SQL Server, not the file name on disk.
  • LogicalName is the internal name SQL Server uses to reference the file. This is set at restore time and often does not match the physical file name.
  • PhysicalName is the full path to the file on disk. This is what you need when you are investigating disk usage at the OS level.
  • FileSizeIn8KBPages is the size column from sys.master_files, expressed in 8 KB pages. To convert to megabytes, multiply by 8 and divide by 1024. To convert to gigabytes, multiply by 8 and divide by 1,048,576.

If you want the size returned in megabytes directly, extend the query like this:

SELECT
    d.name              AS DatabaseName,
    m.name              AS LogicalName,
    m.physical_name     AS PhysicalName,
    m.type_desc         AS FileType,
    (m.size * 8) / 1024 AS FileSizeMB
FROM sys.master_files m
INNER JOIN sys.databases d
    ON m.database_id = d.database_id
ORDER BY m.size DESC;

The type_desc column is a useful addition here. It returns either ROWS for data files or LOG for log files, which makes it easy to filter down to just the log files if that is what you are investigating.

What to Do After You Identify the Offending File

Finding which files belong to which SQL database is the diagnostic step. What you do next depends on the situation, but in the dev environment scenario described above, the typical remediation path looks like this:

  1. Confirm the recovery model. Run SELECT name, recovery_model_desc FROM sys.databases to verify the database is set to FULL recovery in an environment where it should be SIMPLE.

  2. Change the recovery model. If this is a dev or test environment with no requirement for point-in-time recovery, switch to simple recovery:

    ALTER DATABASE [YourDatabaseName] SET RECOVERY SIMPLE;

  3. Shrink the log file. Changing the recovery model alone does not immediately reclaim space. You need to checkpoint the database and then shrink the log file:

    USE [YourDatabaseName];
CHECKPOINT;
DBCC SHRINKFILE (YourLogicalLogFileName, 1);

    Note: shrinking log files is generally something to avoid in production environments because it can cause VLF fragmentation. In a dev environment where the log has ballooned due to neglect, a one-off shrink is reasonable.

  4. Set up a maintenance plan. Even in dev, having a basic maintenance plan that either runs log backups (if full recovery is required) or simply keeps the recovery model appropriate for the environment prevents this from happening again.

What About Databases That Are Not Online?

The sys.master_files view includes files for all databases on the instance, including those that are offline, in recovery, or in a suspect state. If you are troubleshooting a server where a database is not accessible, this query can still tell you where its files are on disk, which is useful when you need to locate files for a manual restore or investigation.

The sys.databases view includes a state_desc column that shows the current database state. Adding that to the query gives you extra context:

SELECT
    d.name              AS DatabaseName,
    d.state_desc        AS DatabaseState,
    m.name              AS LogicalName,
    m.physical_name     AS PhysicalName,
    m.type_desc         AS FileType,
    (m.size * 8) / 1024 AS FileSizeMB
FROM sys.master_files m
INNER JOIN sys.databases d
    ON m.database_id = d.database_id
ORDER BY m.size DESC;

This version is particularly useful during incident response, when you need a fast inventory of the instance and not every database is in a normal state.

Key Takeaways

  • Querying sys.master_files joined to sys.databases is the fastest and most reliable way to find which files belong to which SQL database on any SQL Server instance.
  • The size column in sys.master_files is expressed in 8 KB pages. Multiply by 8 and divide by 1024 to get megabytes.
  • Production databases restored into dev environments retain their recovery model. Without log backups configured, transaction logs will grow unchecked and can fill a disk quickly.
  • The type_desc and state_desc columns add useful context when you are diagnosing disk or availability issues.
  • Shrinking log files in production is generally bad practice due to VLF fragmentation. In dev environments where logs have ballooned due to neglect, a one-off shrink after switching to simple recovery is acceptable.

Queries like this one are part of the standard diagnostic toolkit that DBA Services uses during SQL Server health checks. If your team is dealing with recurring disk space issues, uncontrolled log growth, or databases that have drifted from their intended configuration, a structured health check can surface these problems across your entire SQL Server estate. Get in touch with the DBA Services team to find out what is running unchecked in your environment.