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

User · Answer

This query generates size for both log and data in MB as well as GB SELECT X database name         X log size mb         X log size mb   1024 AS log size gb         X row size mb         X row size mb   1024 AS row size gb         X total size mb         X total size mb   1024 AS total size gb   FROM  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   -- current db by default             GROUP BY database id  AS X

User · Answer

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

User · Answer

EXEC sp spaceused  oneresultset   1  show in 1 row all of the result  if you execute just  EXEC sp spaceused  you will see two rows Work in SQL Server Management Studio v17 9

User · Answer

SELECT      sys databases name AS  Database Name             CONVERT VARCHAR SUM size  8 1024    MB  AS  Size         FROM        sys databases         JOIN        sys master files        ON          sys databases database id sys master files database id        GROUP BY    sys databases name        ORDER BY    sys databases name

User · Answer

You can check how this query works following this link       IF OBJECT ID  tempdb   spacetable   IS NOT NULL      DROP TABLE tempdb   spacetable      create table  spacetable           database name varchar 50        total size data int      space util data int      space data left int      percent fill data float      total size data log int      space util log int      space log left int      percent fill log char 50        total db size  int       total size used  int       total size left  int           insert into   spacetable     EXECUTE master sys sp MSforeachdb  USE          select x  DATABASE NAME  x  total size data  x  space util  x  total size data -x  space util   space left data       x  percent fill  y  total size log  y  space util       y  total size log -y  space util   space left log  y  percent fill       y  total size log  x  total size data    total db size        x  space util  y  space util    total size used         y  total size log  x  total size data  - y  space util  x  space util     total size left        from  select DB NAME     DATABASE NAME        sum size 8 1024    total size data   sum FILEPROPERTY name   SpaceUsed    8 1024    space util        case when sum size 8 1024  0 then   divide by zero   else     substring cast  sum FILEPROPERTY name   SpaceUsed     1 0 100 sum size   as CHAR 50   1 6  end   percent fill       from sys master files where database id DB ID DB NAME     and  type 0     group by type desc    as x        select      sum size 8 1024    total size log   sum FILEPROPERTY name   SpaceUsed    8 1024    space util        case when sum size 8 1024  0 then   divide by zero   else     substring cast  sum FILEPROPERTY name   SpaceUsed     1 0 100 sum size   as CHAR 50   1 6  end   percent fill       from sys master files where database id DB ID DB NAME     and  type 1     group by type desc   y      select   from  spacetable      order by database name     drop table  spacetable

User · Answer

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

User · Answer

If you want to simply check single database size  you can do it using SSMS Gui Go to Server Explorer - gt  Expand it - gt  Right click on Database - gt  Choose Properties - gt  In popup window choose General tab - gt See Size Source  Check database size in Sql server   Various Ways explained

User · Answer

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

User · Answer

SELECT     DB NAME  database id  as  Database Name       name as  Database File Name        Type    CASE WHEN Type Desc    ROWS  THEN  Data File s               WHEN Type Desc    LOG   THEN  Log File s               ELSE Type Desc END      size 8 1024 as  Size  MB        physical name as  Database File Location  FROM sys master files ORDER BY 1 3   Output  Database Name               Database File Name              Type                Size  MB    Database File Location --------------------------- ------------------------------- ------------------- ----------- --------------------------------------------------------------- AdventureWorksDW2017        AdventureWorksDW2017            Data File s         136         E  MSSQL14 MSSQLSERVER MSSQL DATA AdventureWorksDW2017 mdf AdventureWorksDW2017        AdventureWorksDW2017 log        Log File s          72          E  MSSQL14 MSSQLSERVER MSSQL DATA AdventureWorksDW2017 log ldf DBA Admin                   DBA Admin                       Data File s         8           E  MSSQL14 MSSQLSERVER MSSQL DATA DBA Admin mdf DBA Admin                   DBA Admin log                   Log File s          8           E  MSSQL14 MSSQLSERVER MSSQL DATA DBA Admin log ldf EventNotifications          EventNotifications              Data File s         8           E  MSSQL14 MSSQLSERVER MSSQL DATA EventNotifications mdf EventNotifications          EventNotifications log          Log File s          8           E  MSSQL14 MSSQLSERVER MSSQL DATA EventNotifications log ldf master                      master                          Data File s         4           E  MSSQL14 MSSQLSERVER MSSQL DATA master mdf master                      mastlog                         Log File s          2           E  MSSQL14 MSSQLSERVER MSSQL DATA mastlog ldf model                       modeldev                        Data File s         8           E  MSSQL14 MSSQLSERVER MSSQL DATA model mdf model                       modellog                        Log File s          8           E  MSSQL14 MSSQLSERVER MSSQL DATA modellog ldf msdb                        MSDBData                        Data File s         19          E  MSSQL14 MSSQLSERVER MSSQL DATA MSDBData mdf msdb                        MSDBLog                         Log File s          13          E  MSSQL14 MSSQLSERVER MSSQL DATA MSDBLog ldf tempdb                      temp2                           Data File s         8           E  MSSQL14 MSSQLSERVER MSSQL DATA tempdb mssql 2 ndf tempdb                      temp3                           Data File s         8           E  MSSQL14 MSSQLSERVER MSSQL DATA tempdb mssql 3 ndf tempdb                      temp4                           Data File s         8           E  MSSQL14 MSSQLSERVER MSSQL DATA tempdb mssql 4 ndf tempdb                      tempdev                         Data File s         8           E  MSSQL14 MSSQLSERVER MSSQL DATA tempdb mdf tempdb                      templog                         Log File s          8           E  MSSQL14 MSSQLSERVER MSSQL DATA templog ldf

User · Answer

There are already a lot of great answers here but it s worth mentioning a simple and quick way to get the SQL Server Database size with SQL Server Management Studio  SSMS  using a standard report    To run a report you need    right-click on the database go to Reports   Standard Reports   Disk Usage      It prints a nice report     Where the Total space Reserved is the total size of the database on the disk and it includes the size of all data files and the size of all transaction log files   Under the hood  SSMS uses dbo sysfiles view or sys database files view  depending on the version of MSSQL   and some kind of this query to get the Total space Reserved value   SELECT sum  convert dec  19  2     convert bigint SIZE      8192   1048576 0  db size mb FROM dbo sysfiles

User · Answer

Check Database Size in SQL Server for both Azure and On-Premises-  Method 1     Using    sys database files    System View  SELECT     DB NAME   AS  database name       CONCAT CAST SUM          CAST   size   8 0 1024  AS DECIMAL 15 2          AS VARCHAR 20     MB   AS  database size  FROM sys database files      Method 2     Using    sp spaceused    System Stored Procedure  EXEC sp spaceused

[sql] Select SQL Server database size

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to sql-server-2008-r2