Select SQL Server database size

Try this one -

Query:

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

Output:

-- my queryname           log_size_mb  row_size_mb   total_size_mb-------------- ------------ ------------- -------------xxxxxxxxxxx    512.00       302.81        814.81-- sp_spaceuseddatabase_name    database_size      unallocated space---------------- ------------------ ------------------xxxxxxxxxxx      814.81 MB          13.04 MB

Function:

ALTER FUNCTION [dbo].[GetDBSize] (    @db_name NVARCHAR(100))RETURNS TABLEASRETURN  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(@db_name)      OR @db_name IS NULL  GROUP BY database_id

UPDATE 2016/01/22:

Show information about size, free space, last database backups

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL    DROP TABLE #spaceCREATE TABLE #space (      database_id INT PRIMARY KEY    , data_used_size DECIMAL(18,2)    , log_used_size DECIMAL(18,2))DECLARE @SQL NVARCHAR(MAX)SELECT @SQL = STUFF((    SELECT '    USE [' + d.name + ']    INSERT INTO #space (database_id, data_used_size, log_used_size)    SELECT          DB_ID()        , SUM(CASE WHEN [type] = 0 THEN space_used END)        , SUM(CASE WHEN [type] = 1 THEN space_used END)    FROM (        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)        FROM sys.database_files s        GROUP BY s.[type]    ) t;'    FROM sys.databases d    WHERE d.[state] = 0    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')EXEC sys.sp_executesql @SQLSELECT      d.database_id    , d.name    , d.state_desc    , d.recovery_model_desc    , t.total_size    , t.data_size    , s.data_used_size    , t.log_size    , s.log_used_size    , bu.full_last_date    , bu.full_size    , bu.log_last_date    , bu.log_sizeFROM (    SELECT          database_id        , log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))        , data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))        , total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))    FROM sys.master_files    GROUP BY database_id) tJOIN sys.databases d ON d.database_id = t.database_idLEFT JOIN #space s ON d.database_id = s.database_idLEFT JOIN (    SELECT          database_name        , full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)        , full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)        , log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)        , log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)    FROM (        SELECT              s.database_name            , s.[type]            , s.backup_finish_date            , backup_size =                        CAST(CASE WHEN s.backup_size = s.compressed_backup_size                                    THEN s.backup_size                                    ELSE s.compressed_backup_size                        END / 1048576.0 AS DECIMAL(18,2))            , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)        FROM msdb.dbo.backupset s        WHERE s.[type] IN ('D', 'L')    ) f    WHERE f.RowNum = 1    GROUP BY f.database_name) bu ON d.name = bu.database_nameORDER BY t.total_size DESC

Output:

database_id name                             state_desc   recovery_model_desc total_size   data_size   data_used_size  log_size    log_used_size  full_last_date          full_size    log_last_date           log_size----------- -------------------------------- ------------ ------------------- ------------ ----------- --------------- ----------- -------------- ----------------------- ------------ ----------------------- ---------24          StackOverflow                    ONLINE       SIMPLE              66339.88     65840.00    65102.06        499.88      5.05           NULL                    NULL         NULL                    NULL11          AdventureWorks2012               ONLINE       SIMPLE              16404.13     15213.00    192.69          1191.13     15.55          2015-11-10 10:51:02.000 44.59        NULL                    NULL10          locateme                         ONLINE       SIMPLE              1050.13      591.00      2.94            459.13      6.91           2015-11-06 15:08:34.000 17.25        NULL                    NULL8           CL_Documents                     ONLINE       FULL                793.13       334.00      333.69          459.13      12.95          2015-11-06 15:08:31.000 309.22       2015-11-06 13:15:39.000 0.011           master                           ONLINE       SIMPLE              554.00       492.06      4.31            61.94       5.20           2015-11-06 15:08:12.000 0.65         NULL                    NULL9           Refactoring                      ONLINE       SIMPLE              494.32       366.44      308.88          127.88      34.96          2016-01-05 18:59:10.000 37.53        NULL                    NULL3           model                            ONLINE       SIMPLE              349.06       4.06        2.56            345.00      0.97           2015-11-06 15:08:12.000 0.45         NULL                    NULL13          sql-format.com                   ONLINE       SIMPLE              216.81       181.38      149.00          35.44       3.06           2015-11-06 15:08:39.000 23.64        NULL                    NULL23          users                            ONLINE       FULL                173.25       73.25       3.25            100.00      5.66           2015-11-23 13:15:45.000 0.72         NULL                    NULL4           msdb                             ONLINE       SIMPLE              46.44        20.25       19.31           26.19       4.09           2015-11-06 15:08:12.000 2.96         NULL                    NULL21          SSISDB                           ONLINE       FULL                45.06        40.00       4.06            5.06        4.84           2014-05-14 18:27:11.000 3.08         NULL                    NULL27          tSQLt                            ONLINE       SIMPLE              9.00         5.00        3.06            4.00        0.75           NULL                    NULL         NULL                    NULL2           tempdb                           ONLINE       SIMPLE              8.50         8.00        4.50            0.50        1.78           NULL                    NULL         NULL                    NULL

Also compare the results with the following query's result

EXEC sp_helpdb @dbname= 'MSDB'

It produces result similar to the following

There is a good article - Different ways to determine free space for SQL Server databases and database files

Worked perfectly for me to calculate SQL database size in SQL Server 2012

exec sp_spaceused