You are here:Home » tsql » Database size information - SQL Server

Database size information - SQL Server

This is common information which DBA required on a daily basis for monitoring or reporting purposes. This is basic details which help us to get the database growth statistics on daily, weekly and monthly basis so we can have an idea for the disk space management. We can dump daily database statistics and make database size and growth reports. You all know about the script and you may use too, even I would like to share. System stored procedures help us to get the same database information, e.g sp_helpdb stored procedure give details,
EXEC sp_helpdb 'DEMO';

/* Output :

name       db_size   owner  dbid   created     status           compatibility_level
------------------------ ------ ------ ----------- ---------------- -------------------
DEMO       13.25 MB  sa     19     Apr 28 2015 Status=ONLINE,...   110

 
name       fileid filename                                                                      filegroup size     maxsize        growth  usage
-------   -------- ------------------------------------------------------------------------------ --------- ------- ------------   -------- -------
DEMO       1  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO.mdf     PRIMARY   9280 KB  Unlimited      1024 KB data only
DEMO_log   2  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO_log.ldf NULL      4288 KB  2147483648 KB  10%     log only

*/

This is all information for database which returned by sp_helpdb by passing the database name as a parameter. If you do not wish to pass the database name, then it will return all databases without physical file details. Same approach receive by sp_spaceused system stored procedure without any parameter passing.
USE DEMO
GO
EXEC sp_spaceused;

/* Output :

database_name  database_size      unallocated space
-------------- ------------------ ------------------
DEMO           13.25 MB           3.52 MB

reserved      data               index_size         unused
------------- ------------------ ------------------ ---------
5680 KB       3064 KB            2024 KB            592 KB


*/

Now I would like to get it with some other scripts which give details for each database and their physical files.
USE master 
GO 

SELECT db.[name]                  AS 'DBName', 
       af.name                    AS 'LogicalName', 
       af.[filename]              AS 'PhysicalName', 
       ( Cast(( ( ( Cast(af.[size] AS NUMERIC(18, 4)) * 8192 ) / 1024 ) / 1024 ) 
              AS 
                NUMERIC(18, 2)) ) AS 'FileSize_MB' 
FROM   sys.sysdatabases db 
       INNER JOIN sys.sysaltfiles af 
               ON db.dbid = af.dbid 

/* Output :

DBName                LogicalName               PhysicalName                                                                                FileSize_MB
------------------- --------------------------- ---------------------------------------------------------------------------------------- ---------------
master                master                    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\master.mdf                 4.88
master                mastlog                   C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\mastlog.ldf                1.75
tempdb                tempdev                   C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\tempdb.mdf                 8.00
tempdb                templog                   C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\templog.ldf                0.50
model                 modeldev                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\model.mdf                  4.06
model                 modellog                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\modellog.ldf               1.00
msdb                  MSDBData                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\MSDBData.mdf               22.31
msdb                  MSDBLog                   C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\MSDBLog.ldf                19.63
test                  test                      C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test.mdf                   4.06
test                  test_log                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test_log.ldf               1.31
test1                 test1                     C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test1.mdf                  8.00
test1                 test1_log                 C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test1_log.ldf              19.63
Repl1                 Repl1                     C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Repl1.mdf                  23.00
Repl1                 Repl1_log                 C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Repl1_log.ldf              2.50
SBExternal            SBExternal                C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternal.mdf             4.06
SBExternal            SBExternal_log            C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternal_log.ldf         1.02
SBExternalReceiver    SBExternalReceiver        C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternalReceiver.mdf     4.06
SBExternalReceiver    SBExternalReceiver_log    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternalReceiver_log.ldf 1.02
importdb              importdb                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\importdb .mdf              4.06
importdb              importdb _log             C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\importdb _log.ldf          1.02
import                import                    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\import.mdf                 9.06
import                import_log                C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\import_log.ldf             2.81
SchemaSync            SchemaSync                C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync.mdf             4.06
SchemaSync            SchemaSync_log            C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync_log.ldf         1.02
SchemaSync_New        SchemaSync_New            C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync_New.mdf         4.06
SchemaSync_New        SchemaSync_New_log        C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync_New_log.ldf     1.02
hMailServer           hMailServer               C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\hMailServer.mdf            4.06
hMailServer           hMailServer_log           C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\hMailServer_log.ldf        1.02
AdventureWorksLT2008  AdventureWorksLT2008_Data D:\Data\AdventureWorksLT2008_Data.mdf                                                       8.31
AdventureWorksLT2008  AdventureWorksLT2008_Log  D:\Data\AdventureWorksLT2008_Log.ldf                                                        18.00
powershelldb          powershelldb              C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\powershelldb.mdf           4.06
powershelldb          powershelldb_log          C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\powershelldb_log.ldf       1.02
SMOSimple_DB          SMOSimple_DB              C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SMOSimple_DB.mdf           4.06
SMOSimple_DB          SMOSimple_DB_log          C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SMOSimple_DB_log.ldf       1.02
Merge_Repl_Sub        Merge_Repl_Sub            C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Merge_Repl_Sub.mdf         9.00
Merge_Repl_Sub        Merge_Repl_Sub_log        C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Merge_Repl_Sub_log.ldf     3.75
DEMO                  DEMO                      C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO.mdf                   9.06
DEMO                  DEMO_log                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO_log.ldf               4.19

*/

This is details returned by each physical file of all databases, but now the same script with minor changes return whole database size details without physical files detail.
USE master 
GO 

SELECT db.[name]                           AS 'DBName', 
       Sum(( Cast(( ( ( Cast(af.[size] AS NUMERIC(18, 4)) * 8192 ) / 1024 ) / 
                    1024 ) AS 
                        NUMERIC(18, 2)) )) AS 'FileSize_MB' 
FROM   sys.sysdatabases db 
       INNER JOIN sys.sysaltfiles af 
               ON db.dbid = af.dbid 
GROUP  BY db.[name] 

/* Output :

DBName                FileSize_MB
--------------------- -------------
master                6.63
tempdb                8.50
model                 5.06
msdb                  41.94
test                  5.37
test1                 27.63
Repl1                 25.50
SBExternal            5.08
SBExternalReceiver    5.08
importdb              5.08
import                11.87
SchemaSync            5.08
SchemaSync_New        5.08
hMailServer           5.08
AdventureWorksLT2008  26.31
powershelldb          5.08
SMOSimple_DB          5.08
Merge_Repl_Sub        12.75
DEMO                  13.25

*/

These are the scripts which I want to share with you which may help you and I would like you to share your thoughts and scripts which you are using for database size and growth statistics. You may read my earlier post for Database Backup Statistics and History.