Determine free space for MS SQL Server databases

There isn’t a single command that gives you all the information.

sp_helpdb

This procedure will give you the total size of the database and associate files. It doesn’t tell you how much free space there is

sp_spaceused

This procedure will show you how much free space there is in the database. Note, it includes transaction log free space.

DBCC SQLPERF

This command will provide information about log files only for each database,

sys.database_files & FILEPROPERTY

Use this query to calculate the free space. Note, “size” is an integer for the current size of the file in 8 KB pages.

USE master
GO

SELECT
	DB_NAME() AS DatabaseName,
	name AS DBName,
	size,
	max_size,
	growth,
	FILEPROPERTY(name, 'SpaceUsed') AS SpaceUsed,
	size - FILEPROPERTY(name, 'SpaceUsed') AS FreeSpace
FROM
	sys.database_files