SQL Server 2008: How to query all databases sizes?

SqlSql ServerTsqlSql Server-2008-R2

Sql Problem Overview

I have MS SQL 2008 R2, 500 databases. What is the most efficient, easiest and 'modern' way to query all databases sizes.

The output should have columns:

  • DatabaseName
  • DataFilesSize
  • LogFilesSize

Sql Solutions

Solution 1 - Sql

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db

Solution 2 - Sql

I don't know exactly what you mean by efficiency but this is straightforward and it works for me:

SELECT
    DB_NAME(db.database_id) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
    LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

With results like:

DatabaseName  RowSizeMB LogSizeMB StreamSizeMB TextIndexSizeMB
------------- --------- --------- ------------ ---------------
master        4         1.25      NULL         NULL
model         2.25      0.75      NULL         NULL
msdb          14.75     8.1875    NULL         NULL
tempdb        8         0.5       NULL         NULL

Note: was inspired by this article

Solution 3 - Sql

Here's a simple, quick and reliable query that will give all database and log file names, sizes and also database statuses (e.g. ONLINE) in a nice, easy to read output:

SELECT
    D.name,
    F.Name AS FileType,
    F.physical_name AS PhysicalFile,
    F.state_desc AS OnlineStatus,
    CAST(F.size AS bigint) * 8*1024 AS SizeInBytes,
    CAST((F.size*8.0)/1024/1024 AS decimal(18,3)) AS SizeInGB
FROM 
    sys.master_files F
    INNER JOIN sys.databases D ON D.database_id = F.database_id
ORDER BY SizeInBytes desc

Solution 4 - Sql

with Total Database size ordered Desc

SELECT     
DB_NAME(db.database_id) DatabaseName,     
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,     
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB, 
(CAST(mfrows.RowSize AS FLOAT)*8)/1024/1024+(CAST(mflog.LogSize AS FLOAT)*8)/1024/1024 DBSizeG,
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,     
(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB 
FROM sys.databases db     
LEFT JOIN (SELECT database_id, 
				  SUM(size) RowSize 
			FROM sys.master_files 
			WHERE type = 0 
			GROUP BY database_id, type) mfrows 
	ON mfrows.database_id = db.database_id     
LEFT JOIN (SELECT database_id, 
				  SUM(size) LogSize 
			FROM sys.master_files 
			WHERE type = 1 
			GROUP BY database_id, type) mflog 
	ON mflog.database_id = db.database_id     
LEFT JOIN (SELECT database_id, 
		          SUM(size) StreamSize 
		          FROM sys.master_files 
		          WHERE type = 2 
		          GROUP BY database_id, type) mfstream 
	ON mfstream.database_id = db.database_id     
LEFT JOIN (SELECT database_id, 
                  SUM(size) TextIndexSize 
                  FROM sys.master_files 
                  WHERE type = 4 
                  GROUP BY database_id, type) mftext 
    ON mftext.database_id = db.database_id 
       ORDER BY 4 DESC

Solution 5 - Sql

All seem overly complicated! Or am I missing something?

Surely all you need is something like:

select d.name, case when m.type = 0 then 'Data' else 'Log' end,  m.size * 8 / 1024
from sys.master_files m JOIN sys.databases d ON d.database_id = m.database_id

or if you don't want the log:

select d.name, m.size * 8 / 1024
from sys.master_files m JOIN sys.databases d ON d.database_id = m.database_id and m.type =0

Solution 6 - Sql

SELECT
	DB.name,
	SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB,
	SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB
FROM
	sys.master_files MF
	JOIN sys.databases DB ON DB.database_id = MF.database_id
GROUP BY DB.name
ORDER BY DataFileSizeMB DESC

Solution 7 - Sql

please find more deatils or download the script from below link https://gallery.technet.microsoft.com/SIZE-OF-ALL-DATABASES-IN-0337f6d5#content

 DECLARE @spacetable table
 (
 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 ''less than 1% used'' 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 ''less than 1% used'' 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

Solution 8 - Sql

I recently came across this page looking for something like this. Just in case someone comes across this and is interested in only the core user databases, you can use something like this which excludes Master, msdb...

SELECT
DB_NAME(db.database_id) DatabaseName,
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB
FROM sys.databases db
LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
where DB_NAME(db.database_id) not like 'master'
and DB_NAME(db.database_id) not like 'msdb'
and DB_NAME(db.database_id) not like 'model'
and DB_NAME(db.database_id) not like 'tempdb'
and DB_NAME(db.database_id) not like 'Northwind'
and DB_NAME(db.database_id) not like 'ReportServer'
order by DB_NAME(db.database_id)

Solution 9 - Sql

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
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
ORDER BY t.total_size DESC

Solution 10 - Sql

Not to steal your answer and adapt it for points or anything, but here is another factorization:

select d.name, 
    sum(m0.size*8.0/1024) data_file_size_mb, 
    sum(m1.size*8.0/1024) log_file_size_mb 
from sys.databases d
inner join sys.master_files m0 on m0.database_id = d.database_id
inner join sys.master_files m1 on m1.database_id = d.database_id
where m0.type = 0 and m1.type = 1
group by d.name, d.database_id
order by d.database_id

Solution 11 - Sql

Simplified & Improved version:

SELECT
    D.name,
    CAST(SUM(F.size) AS bigint) * 8*1024 AS SizeInBytes,
    CAST(SUM(F.size*8.0)/1024/1024 AS decimal(18,3)) AS SizeInGB
FROM 
    sys.master_files F
    INNER JOIN sys.databases D ON D.database_id = F.database_id
GROUP BY D.name
ORDER BY SizeInBytes desc

Solution 12 - Sql

A better and quite simpler one

SELECT [Database Name] = DB_NAME(database_id),
     [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
               WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'
               ELSE Type_Desc END,
     [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )
FROM   sys.master_files
--Uncomment if you need to query for a particular database
--WHERE      database_id = DB_ID(‘Database Name’) 
GROUP BY  GROUPING SETS
        (
               (DB_NAME(database_id), Type_Desc),
               (DB_NAME(database_id))
        ) ORDER BY      DB_NAME(database_id), Type_Desc DESC

It will give you size of Data File(s) and Log File(s) separately like below

DatabaseName	Type			Size in MB
-------------------------------------------
FMS				Data File(s)	23.00
FMS				Log File(s)		1.50
PointOfSale		Data File(s)	4.00
PointOfSale		Log File(s)		1.25
Union2			Data File(s)	336.00
Union2			Log File(s)		1191.13
SurveyProject	Data File(s)	4.00
SurveyProject	Log File(s)		1.00

Solution 13 - Sql

The following code worked for me very well.

    SELECT
        D.name As DbName,
        F.Name AS FullDbName,
    	CASE WHEN F.type_desc='ROWS' THEN 'mdf' ELSE 'ldf' END AS FileType,
        F.physical_name AS PhysicalFile,
    	CONVERT(DATE,D.create_date) AS CreationDate,
        F.state_desc AS OnlineStatus,
        CAST((F.size*8)/1024 AS VARCHAR(26)) + ' MB' AS FileSize_MB,
        CAST(F.size*8 AS VARCHAR(32)) + ' Bytes' AS FileSize_Bytes,
    	CAST(CAST(ROUND((F.size*8)/(1024.0*1024.0),0) AS INT) AS VARCHAR(32)) + ' GB' AS FileSize_GB
    
    FROM 
        sys.master_files F
        INNER JOIN sys.databases D ON D.database_id = F.database_id
    
    ORDER BY
         D.name

Solution 14 - Sql

All this examples work fine on most of my database servers. However if you have 1 server with multiple instances and all those servers those query's give you no result.

For instance the first query gives a result like:

name	DataFileSizeMB	LogFileSizeMB
master	NULL	NULL
tempdb	NULL	NULL
model	NULL	NULL
msdb	NULL	NULL

So the databases are selected from sys.databases, however the table sys.master_files seems to be empty or gives no result.

Even a simple query as select * from sys.master_files has a result of 0 records. You don't receive any errors but no records are found. On my servers with only 1 database instance this works fine.

Solution 15 - Sql

sometimes SECURITY issues prevent from asking for all the db's and you need to query one by one with the db prefix, for those cases i created this dynamic query

go
declare @Results table ([Name] nvarchar(max), [DataFileSizeMB] int, [LogFileSizeMB] int);

declare @QaQuery nvarchar(max)
declare @name nvarchar(max)

declare MY_CURSOR cursor 
  local static read_only forward_only
for 
select name from master.dbo.sysdatabases where name not in ('master', 'tempdb', 'model', 'msdb', 'rdsadmin');

open MY_CURSOR
fetch next from MY_CURSOR into @name
while @@FETCH_STATUS = 0
begin 
	if(len(@name)>0)
	begin
		print @name + ' Column Exist'
		set @QaQuery = N'select 
							'''+@name+''' as Name
							,sum(case when type = 0 then size else 0 end) as DataFileSizeMB
							,sum(case when type = 1 then size else 0 end) as LogFileSizeMB
						from ['+@name+'].sys.database_files
						group by replace(name, ''_log'', '''')';

		insert @Results exec sp_executesql @QaQuery;
	end
  fetch next from MY_CURSOR into @name
end
close MY_CURSOR
deallocate MY_CURSOR

select * from @Results order by DataFileSizeMB desc
go
Categories
Recommended Sql Solutions
Recommended Sql Server Solutions
Recommended Tsql Solutions
Recommended Sql Server-2008-R2 Solutions

Previous Solution:

Align button at the bottom of div using CSS

CssButtonAlignment

Next Solution:

Detect Android phone via Javascript / jQuery

JavascriptJqueryAndroid

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAlex AzaView Question on Stackoverflow
Solution 1 - SqlAlex AzaView Answer on Stackoverflow
Solution 2 - SqlJeffyView Answer on Stackoverflow
Solution 3 - SqlChris HalcrowView Answer on Stackoverflow
Solution 4 - SqlsamView Answer on Stackoverflow
Solution 5 - SqlGarry_GView Answer on Stackoverflow
Solution 6 - SqlbjnrView Answer on Stackoverflow
Solution 7 - SqlPruthvi RajView Answer on Stackoverflow
Solution 8 - SqldaffyjejeView Answer on Stackoverflow
Solution 9 - SqlDevartView Answer on Stackoverflow
Solution 10 - SqlsamusView Answer on Stackoverflow
Solution 11 - SqlAlex RView Answer on Stackoverflow
Solution 12 - SqlSheikh M. HarisView Answer on Stackoverflow
Solution 13 - SqlAmin GolmahalleView Answer on Stackoverflow
Solution 14 - SqlFrank KaagmanView Answer on Stackoverflow
Solution 15 - SqlYakir ManorView Answer on Stackoverflow