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:-
SELECT
db.name AS DBName,
type_desc AS FileType,
Physical_Name AS Location
FROM
sys.master_files mf
INNER JOIN
sys.databases db ON db.database_id = mf.database_id
