Database File Information

by Jason S, posted in Database Administration, T-SQL

I often go out to clients and have to do some quick analysis of the client’s SQL Servers.  Below is one of the SQL scripts that I’ll use when I want some to give some quick feedback on whether the files and their properties are configured properly.

Not a very tricky script but something that I do use quite often.

WITH DatabaseFiles
AS (
SELECT
UPPER(LEFT(physical_name, 1)) as drive_letter
,type_desc
,DB_NAME(database_id) as database_name
,CAST((size * CONVERT(float,8))/1024. as decimal(12,2)) as size
,NULLIF(max_size, -1) as max_size
,CASE WHEN growth = 0 THEN 'FIXED SIZE'
WHEN is_percent_growth = 1 THEN CONVERT(varchar(20), growth) + '%'
ELSE CONVERT(varchar(20), growth*8/1024) + ' MB' END as growth
,name as file_name
,physical_name
FROM sys.master_files
)
SELECT *
FROM DatabaseFiles
ORDER BY drive_letter, type_desc, database_name;

And why do I use a CTE?  Because it helps when I want to filter on calculated fields and want to make changes to the output without changing the core of the result set.