Select SQL Server database size

Question

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?

Answer 1

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 query name           log_size_mb  row_size_mb   total_size_mb -------------- ------------ ------------- ------------- xxxxxxxxxxx    512.00       302.81        814.81  -- sp_spaceused database_name    database_size      unallocated space ---------------- ------------------ ------------------ xxxxxxxxxxx      814.81 MB          13.04 MB 

Function:

ALTER FUNCTION [dbo].[GetDBSize]  (     @db_name NVARCHAR(100) ) RETURNS TABLE AS RETURN    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 #space  CREATE 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 @SQL  SELECT       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_size FROM (     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 ) t JOIN sys.databases d ON d.database_id = t.database_id LEFT JOIN #space s ON d.database_id = s.database_id LEFT 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_name ORDER 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                    NULL 11          AdventureWorks2012               ONLINE       SIMPLE              16404.13     15213.00    192.69          1191.13     15.55          2015-11-10 10:51:02.000 44.59        NULL                    NULL 10          locateme                         ONLINE       SIMPLE              1050.13      591.00      2.94            459.13      6.91           2015-11-06 15:08:34.000 17.25        NULL                    NULL 8           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.01 1           master                           ONLINE       SIMPLE              554.00       492.06      4.31            61.94       5.20           2015-11-06 15:08:12.000 0.65         NULL                    NULL 9           Refactoring                      ONLINE       SIMPLE              494.32       366.44      308.88          127.88      34.96          2016-01-05 18:59:10.000 37.53        NULL                    NULL 3           model                            ONLINE       SIMPLE              349.06       4.06        2.56            345.00      0.97           2015-11-06 15:08:12.000 0.45         NULL                    NULL 13          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                    NULL 23          users                            ONLINE       FULL                173.25       73.25       3.25            100.00      5.66           2015-11-23 13:15:45.000 0.72         NULL                    NULL 4           msdb                             ONLINE       SIMPLE              46.44        20.25       19.31           26.19       4.09           2015-11-06 15:08:12.000 2.96         NULL                    NULL 21          SSISDB                           ONLINE       FULL                45.06        40.00       4.06            5.06        4.84           2014-05-14 18:27:11.000 3.08         NULL                    NULL 27          tSQLt                            ONLINE       SIMPLE              9.00         5.00        3.06            4.00        0.75           NULL                    NULL         NULL                    NULL 2           tempdb                           ONLINE       SIMPLE              8.50         8.00        4.50            0.50        1.78           NULL                    NULL         NULL                    NULL