Today, one of my colleague asked me how to find the database size in SQL Server using T-SQL.

The below script would help you to get the information:

SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id

EDITed on 22nd Aug 2016
The below script can be used to identify the same for all databases:

CREATE TABLE TEMP_FILESIZE(DBNAME sysname, log_size varchar(100),row_size varchar(100), total_size varchar(100))
Insert into TEMP_FILESIZE
EXEC sp_msforeachdb '
IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
begin 

		SELECT 
			  database_name = ''?''
			, log_size_mb = CAST(SUM(CASE WHEN type_desc = ''LOG'' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
			, row_size_mb = CAST(SUM(CASE WHEN type_desc = ''ROWS'' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
			, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
		FROM ?.sys.master_files WITH(NOWAIT)
		WHERE database_id = DB_ID(''?'') -- for current db 
		GROUP BY database_id
end 
' 
Select * From TEMP_FILESIZE Order by log_size desc
DROP Table TEMP_FILESIZE

Added on 20th Sep 2022
The below script can be used to identify the free space for a database:

SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys. database_files
WHERE type IN (0,1);