Select SQL Server database size

by

how can i query my sql server to only get the size of database?

I used this : 

use "MY_DB"
exec sp_spaceused

I got this :

database_name   database_size   unallocated space
My_DB           17899.13 MB 5309.39 MB

It returns me several column that i don’t need, maybe there is a trick to select database_size column from this stored procedure ?

I also tried this code :

SELECT DB_NAME(database_id) AS DatabaseName,
       Name AS Logical_Name,
       Physical_Name,
       (size * 8) / 1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'MY_DB'

It gives me this result:

DatabaseName    Logical_Name    Physical_Name                    SizeMB
MY_DB           MY_DB           D:\MSSQL\Data\MY_DB.mdf          10613
MY_DB           MY_DB_log       D:\MSSQL\Data\MY_DB.ldf          7286

So i wrote this: 

SELECT SUM(SizeMB)
FROM (
    SELECT DB_NAME(database_id) AS DatabaseName,
           Name AS Logical_Name,
           Physical_Name,
           (size * 8) / 1024 SizeMB
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'MY_DB'
) AS TEMP

I got: 1183

So it works but maybe there is a proper way to get this?

12 Answers
12

Leave a Comment