SQLServer2008: 如何查询所有数据库的大小？

我有微软 SQL 2008 R2,500数据库。 查询所有数据库大小的最有效、最简单和最现代的方法是什么。

输出应该有列:

  • DatabaseName
  • DataFilesSize 数据文件大小
  • LogFilesSize
357825 次浏览
小开

我不知道你说的效率到底是什么意思，但这很简单，而且对我很有效:

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

结果是:

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

注: 灵感来自 这篇文章

小开
最佳答案 
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
小开

与总数据库大小订购 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
小开 
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
小开

我最近偶然发现了这个页面，正在寻找类似的东西。 只是为了以防有人遇到这个，并且只对核心用户数据库感兴趣，您可以使用这样的东西，其中不包括主数据库，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)
小开

下面是一个简单、快速、可靠的查询，它将提供所有数据库和日志文件的名称、大小以及数据库状态(例如 ONLINE) ，并且输出一个漂亮、易于阅读的输出:

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
小开 
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
小开

看起来都太复杂了! 还是我漏掉了什么？

当然，你所需要的只是这样的东西:

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

或者如果你不想要日志:

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
小开

请找到更多的细节或从下面的链接下载脚本 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
小开

有时候，保安问题阻止要求所有的数据库的，你需要一个一个的查询与数据库前缀，为这些情况下，我创建了这个动态查询

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
小开

不是为了窃取你的答案，或者为了得分或者其他什么，但是这里有另外一个因子分解:

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
小开

一个更好更简单的方法

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

它会分别显示数据文件和日志文件的大小，如下所示

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
小开

下面的代码对我非常有用。

    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
小开

简化及改良版本:

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
小开

所有这些示例在我的大多数数据库服务器上都能正常工作。 但是，如果您有一个具有多个实例的服务器和所有这些服务器，那么这些查询不会给您任何结果。

例如，第一个查询给出的结果如下:

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

因此数据库是从 sys.databases中选择的，但是表 sys.master_files似乎是空的或者没有给出结果。

即使像 select * from sys.master_files这样的简单查询也会得到0条记录的结果。 您不会收到任何错误，但是找不到任何记录。 在我只有一个数据库实例的服务器上，这个工作得很好。


提交答案