SQL Server – List Database Size

Background

Need to show a tally of database sizes, here is what we have thus far.

Code

Script


; with cteFile
(
      [database]
    , [fileType]
    , [filename]
    , [size]
 
)
as
(
  
    select
              [database]
                = db_name(tblMF.database_id)
  
            , tblMF.[type_desc]
  
            , [filename]
                = tblMF.physical_name
  
            , [size]
                = tblMF.size
  
    from   master.sys.master_files tblMF
  
) 
  
select
 
          [database] 
 
        , [dataInGB]
            = 
                cast
                (
                    ( [ROWS] * 1.000/128 ) 
                        / 1000
                    as decimal(20, 3)
                )
 
        , [logInGB]
             =  cast
                (
                    ( [LOG] * 1.000/128 ) 
                        / 1000
                    as decimal(20, 3)
                )
 
        , [fileStreamInGB]
            = cast
                (
                    ( isNull([FILESTREAM], 0) * 1.000/128 ) 
                        / 1000
                    as decimal(20, 3)
                )
from
        (
            select
  
                  [database] 
                , [fileType] 
                , [size]
                    = isNull(size, 0)
  
            from   cteFile
 
            where fileType in
                    ( 
                          'ROWS'
                        , 'LOG'
                        , 'FILESTREAM'
                    )
         
        ) tblA
 
PIVOT
(
    sum(
            [size]
       )
    FOR [fileType] in
            ( 
                  ROWS
                , LOG
                , FILESTREAM 
            )
) AS pvtTable
 
order by
        [database] asc


Output

 

Commentary

We are using the master.sys.master_files to quickly get stats on all databases in a SQL Server Instance.

Unfortunately for the FileStream file type the size is left at 0.

 

Connect Item

  1. sys.master_files does not show accurate size information
    • ID :- 377223
    • Link :- Link
    • Opened By :- Michael Hotek
    • Status :- Closed
    • Type :- Bug
    • Opened On :- 2015-Nov-13th
  2. Filestream file does not have correct max file size after restoring and then standbying database – by RansomingSQL
    • ID :- 3131240
    • Link :- Link
    • Opened By :- RansomingSQL
    • Status :- Active
    • Type :- Bug
    • Opened On :- 2017-March-29th

2 thoughts on “SQL Server – List Database Size

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s