Script – Check free space in database files

Below is a script I commonly use to check for the free space in every file of a SQL Server database. The column headings are self-explanatory.

SELECT [Name]
	,physical_name AS [FileName]
	,size * CONVERT(FLOAT, 8) / 1024 AS [Size (MB)]
	,CAST(FILEPROPERTY([name], 'SpaceUsed') AS FLOAT) * CONVERT(FLOAT, 8) / 1024 AS [UsedSpace (MB)]
	,((size * CONVERT(FLOAT, 8)) - (CAST(FILEPROPERTY([name], 'SpaceUsed') AS FLOAT) * CONVERT(FLOAT, 8))) * 100 / (size * CONVERT(FLOAT, 8)) AS [FreePercent]
	,file_id AS [ID]
FROM sys.database_files
ORDER BY [type] ASC

Note that this can also be used for Azure SQL Database and Azure SQL Managed Instance.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

SQL Shots with Dapinder Arora | SQL Server Solutions