Check database size and free space in SQL Server

Every day we have to once check database free space and below is the best query to give detail

Here is a simple SQL script to retrieve the free spaces on db files (data, log) of any database.

Query:  Quick Download


if convert(varchar(20),SERVERPROPERTY('productversion')) like '8%'
SELECT [name], fileid, filename, [size]/128 AS 'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128)/([size]/128))*100)) AS 'percentage Used'
FROM sysfiles
else
SELECT [name], file_id, physical_name, [size]/128 AS 'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128)/([size]/128))*100)) AS 'percentage Used'
FROM sys.database_files


Output:

name	file_id	physical_name	Total Size in MB	Available Space In MB	Used Space In MB	percentage Used
master	1	C:\DATA\master.mdf	4	1.125	2.875	50
mastlog	2	C:\DATA\mastlog.ldf	1	0.46875	0.53125	0

You can refer below for more scripts handy:

Frequently used DMV's in SQL Server