How to get database allocated and unallocated free size detail

We can use below script to get database complete free space with allocated and un-allocated details. 

 

This line specifies the table from which the data will be retrieved. The sysfiles table is a system table in SQL Server that contains information about the database files for each database on the server. Overall, this query provides useful information about the size and usage of the database files on a SQL Server instance.

12345678910111213141516 SELECT @@servername,getdate (),RTRIM(name) AS [Name], groupid AS [Group Id], filename AS [File Name],
   CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],
   CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
   CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
   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]

FROM sysfiles

-- dbcc freesystemcache ('all' ) with mark_in_use_for_removal
-- dbcc opentran 
/*
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 4639)
GO
*/ 

Test format of the query:

SELECT @@servername,getdate (),RTRIM(name) AS [Name], groupid AS [Group Id], filename AS [File Name],

   CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],

   CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],

   CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],

   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]

FROM sysfiles

-- dbcc freesystemcache ('all' ) with mark_in_use_for_removal

-- dbcc opentran 

/*

USE [tempdb]

GO

DBCC SHRINKFILE (N'tempdev' , 4639)

GO

*/