SQL Server - T-SQL script to get space usage details for all databases in an instance

Hi and a warm welcome to all.

In today's post I'll be writing about a very common and one of the most asked information i.e. the space used by databases. In almost every organization that I've worked with, there was someone who seeked this information for one reason or another.

So, you know if you have even more than just 10 databases in your MS SQL Instance then collating such information by "right-click-properties" method is a time consuming task. So, below are two scripts which help you to get all the required details very quickly and precisely.

First script is a more simpler version which will be helpful in-case you need total space (only) consumed by databases or database files.

--===========================================================

create table #t1(db_name varchar(255), file_id tinyint, type_desc char(10), logical_name varchar(255),physical_name varchar(1000), state_desc char(10), size int)

insert into #t1

exec sp_msforeachdb 'use [?]

select db_name() as [db_name], file_id, type_desc, name, physical_name, state_desc, size from sys.database_files'

select [db_name], name, physical_name, (size * 8) as file_size_in_MB from #t1

drop table #t1

--===========================================================

The script above will give you the list of all databases along with their all database files and its corresponding location and file size. Grouping the same table by database name will tell you the total database size.

select [db_name], sum(size * 8) as db_size_in_MB from #t1 group by [db_name]

However, in case you need information like free space as well along with total size, then the below script will be more useful.

--================================================================

create table #temp(db_name varchar(255),db_size_in_mb varchar(100), unallocated_space_in_mb varchar(100))

insert into #temp

exec sp_MSforeachdb

'use ?

declare @id int   -- The object id that takes up space 

  ,@type character(2) -- The object type. 

  ,@pages bigint   -- Working variable for size calc. 

  ,@dbname sysname 

  ,@dbsize bigint 

  ,@logsize bigint 

  ,@reservedpages  bigint 

  ,@usedpages  bigint 

  ,@rowCount bigint 

 

if @id is null 

begin 

 select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) 

  , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) 

  from dbo.sysfiles 

 

 select @reservedpages = sum(a.total_pages), 

  @usedpages = sum(a.used_pages), 

  @pages = sum( 

    CASE 

     -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size" 

     When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0 

     When a.type <> 1 Then a.used_pages 

     When p.index_id < 2 Then a.data_pages 

     Else 0 

    END 

   ) 

 from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id 

  left join sys.internal_tables it on p.object_id = it.object_id 

 

 /* unallocated space could not be negative */ 

 select  

  database_name = db_name(), 

  database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))  

   * 8192 / 1048576,15,2)), 

  unallocated_space = ltrim(str((case when @dbsize >= @reservedpages then 

   (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))  

   * 8192 / 1048576 else 0 end),15,2)) 

 

end 

  '

 

select db_name, CONVERT(decimal(18,2),db_size_in_mb) "total_db_size_in_mb", (CONVERT(decimal(18,2), db_size_in_mb)-CONVERT (decimal(18,2), unallocated_space_in_mb)) as "used_space_in_mb", unallocated_space_in_mb as "free_space_in_mb" from #temp

order by 2 desc

drop table #temp

--=============================================================

*Above query has been revised

Yet another example where T-SQL helps in getting the work-done quickly.