LLI Wiki — My Digital Knowledge Base

Check database file sizes, free space and autogrowth

T-SQL query that shows, for each data and log file of the current database, the size, the used and free space, and the autogrowth setting.

For each file of the current database (data and transaction log), this query returns the size, the space used and free, and the autogrowth setting. Run it in the context of each database, because sys.database_files and FILEPROPERTY only return the files of the active database.

SELECT DB_NAME() AS [database_name], 
	name AS [file_name], 
	[type_desc],
	[physical_name],
	CONVERT(DECIMAL(10,2),SIZE/128.0) AS [current_size_MB], 
	CONVERT(DECIMAL(10,2),CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS [space_used_MB],
	CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(SIZE/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [percent_used],
	CONVERT(DECIMAL(10,2),size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS [free_space_MB],
	CONVERT(DECIMAL(10,2),((SIZE/128.0 - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0)/(SIZE/128.0))*100) AS [percent_free],
	'By ' + CASE is_percent_growth
		WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' 
		WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' 
		ELSE '' END 
	  + CASE max_size 
		WHEN 0 THEN 'DISABLED' 
		WHEN -1 THEN ' Unrestricted' 
		ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END AS [auto_growth]
	FROM sys.database_files;

Notes

Mastodon LLIMastodon SocialFosstodon