Different ways to get the size of the data , log files and Databases

• Using Counters
• Querying sysaltfiles & sys.databases
• Querying sys.master_files

SELECT instance_name AS DatabaseName,
 [Data File(s) Size (KB)]/1024.00 [DataInMB],
 [LOG File(s) Size (KB)]/1024.00 [LogInMB],
 [Data File(s) Size (KB)]/1024.00 + [LOG File(s) Size (KB)]/1024.00 [TotalSize]
FROM
(
 SELECT *
 FROM sys.dm_os_performance_counters
 WHERE counter_name IN ('Data File(s) Size (KB)', 'Log File(s) Size (KB)' )
 AND instance_name != '_Total'
) AS Src
PIVOT
(
 MAX(cntr_value)
 FOR counter_name IN
 ( [Data File(s) Size (KB)], [LOG File(s) Size (KB)] )
 )AS pvt

USE MASTER
Go
SELECT CONVERT(VARCHAR(25), DB.name) AS DatabaseName,
(SELECT SUM((size*8)/1024.00) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB],
(SELECT SUM((size*8)/1024.00) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB],
(SELECT SUM((size*8)/1024.00) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024.00) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB
FROM sys.databases DB
ORDER BY DatabaseName

SELECT 
 DatabaseName = DB_NAME(database_id)
 ,[Data MB] = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
 ,[Log MB] = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
 , [TotalSizeMB] = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files 
GROUP BY database_id
ORDER BY DatabaseName