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:
This question is related to
sql
sql-server
tsql
sql-server-2008-r2
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.
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
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
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
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
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
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
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)
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
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
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
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
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
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
Source: Stackoverflow.com