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.