Today I needed a query to see what the size and growth settings were for our databases. The query below will output name, size and growth information.
SELECT SUBSTRING([name], 0, 40) as [Name],
(([size]*8)/1024) as SizeMB,
CASE [growth]
WHEN 0 THEN 'Fixed Size'
ELSE
CASE [is_percent_growth]
WHEN 0 THEN 'Absolute growth: ' + CAST([growth] as varchar)
WHEN 1 THEN 'Percentage growth: ' + CAST([growth] as varchar)
END
END as [GrowthInfo]
FROM sys.database_files
Output looks like this:
Name SizeMB GrowthInfo
---------------------------------------- ----------- -------------------------------------------------
AdventureWorks 10 Fixed Size
AdventureWorks_log 5 Fixed Size