Use below query to get the database file size. I have used system view “sysaltfiles” from “master” database which keeps information about each and every database file available in current SQL Server instance.
SELECT DB_NAME(dbid) AS DatabaseName,
Name AS LogicalFileName,
CASE WHEN GroupID=1 THEN 'Data' ELSE 'Log' END AS FileType,
FileName AS FilePath, size AS TotalPage,
(Size*8192E)/1048576 AS FileSizeInMB
FROM master..SysAltFiles
SELECT DB_NAME(dbid) AS DatabaseName,
Name AS LogicalFileName,
CASE WHEN GroupID=1 THEN 'Data' ELSE 'Log' END AS FileType,
FileName AS FilePath, size AS TotalPage,
(Size*8192E)/1048576 AS FileSizeInMB
FROM master..SysAltFiles
