Determine where SQL files are

Determine where SQL files are

SQL Files

A typical SQL Server Database comprises two physical files on the disks: an ‘mdf’ file and a ‘ldf’ file. The ‘mdf’ file contains the database scheme, tables, stored procedures, etc., and the ‘ldf’ contains the log file.

It is best to put these files on different disks for optimal performance.

I like to keep things simple in production environments and create and maintain separate customer databases on separate servers. Each server is provisioned with two disks, so the data file goes on one and the log on another.

In test environments, I usually do not do this. A single database server will host several, sometimes up to 50, databases. As those databases grow, historically, I have either increased the disk sizes or added additional ones. When I add extra disks, the fun begins, as it can be difficult to know where the files for a given database are stored.

The solution

The SQL code below displays where the data and log files are on the physical disks:-

    type_desc AS FileType,
    Physical_Name AS Location
    sys.master_files mf
    sys.databases db ON db.database_id = mf.database_id


Hi, my name is Stephen Finchett. I have been a software engineer for over 30 years and worked on complex, business critical, multi-user systems for all of my career. For the last 15 years, I have been concentrating on web based solutions using the Microsoft Stack including ASP.Net, C#, TypeScript, SQL Server and running everything at scale within Kubernetes.