Friday, October 2, 2015

Query to get the size of All databases in an instance

The following query can list the databases (over 1 GB) in an instance along with their size.

select DatabaseName,
       DBSize
From ( Select db_name(mf.database_id) As DatabaseName
      , CONVERT(varchar(30),convert(int, sum(mf.size) * 8 / 1000)/1000) + ' GB' As DBSize
      , cast(getdate() As date) As SizeDate
   From sys.master_files                mf
Where mf.state = 0
Group By
mf.database_id) a
Where DBSize <> '0 GB'