How to find current database size details in SQL Server
Every database will have free space in it depending upon the growth style and type of activities going on the database.
If database has more delete operations on it and growth is depending upon the percentage then some times we may see huge free space in the database.
Below query will give the details.
SELECT GETDATE() Report_Date ,a.FILE_ID AS [FileId],
a.name AS [LogicalName],
CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)) AS [FileSize(MB)],
CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,''
+'SpaceUsed'+'')/128.000,2)) AS [SpaceUsed(MB)], CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,''
+'SpaceUsed'+''))/128.000,2)) AS [FreeSpace(MB)], ROUND((CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,''
+'SpaceUsed'+'')/128.000,2))/ CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))*100,2) AS [Pct_SpaceUsed],
ROUND((CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,''
+'SpaceUsed'+''))/128.000,2))/ CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))*100,2) AS [Pct_FreeSpace] FROM sys.database_files a LEFT OUTER JOIN sys.data_spaces b ON a.data_space_id = b.data_space_id
