SQL Server - Database Size

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 sometimes 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