Database Files

There are times when you need to get information on the actual files that a database is using.

SELECT database_id,
       DB_NAME(database_id) AS database_name,
       file_id,
       type_desc,
       data_space_id,
       name,
       physical_name,
       UPPER(LEFT(physical_name, 3)) AS drive,
       state_desc,
       (size * 8) AS "Size Kb",
       ((size * 8) / 1024) AS "Size Mb"
FROM sys.master_files
ORDER BY database_id, type, data_space_id

• "database_name", simply gives me the actual database name which is just handy
• "drive", because I have had problems where a lower case L looks like I and clarify which drive
• "Size Kb", the actual file size in Kb, SQL Server uses 8Kb pages and the size column contains the number of pages
• "Size Mb", as "Size Kb" but in Mb instead

Note that you can use sys.database_files instead of sys.master_files, they are the same except that database_files only looks at the current database, master_files looks at every database in the SQL Instance. Information is available at sys.master_files (Transact-SQL) which explains the columns.