MS SQL 2008 R2, 500 데이터베이스가 있습니다. 모든 데이터베이스 크기를 쿼리하는 가장 효율적이고 가장 쉽고 ‘현대적인’방법은 무엇입니까?
출력에는 다음 열이 있어야합니다.
- 데이터베이스 이름
- DataFilesSize
- LogFilesSize
답변
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답변
효율성이 무엇을 의미하는지 정확히 모르지만 이것은 간단하며 저에게 효과적입니다.
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참고 : 이 기사 에서 영감을 얻었습니다.
답변
다음은 모든 데이터베이스 및 로그 파일 이름, 크기 및 데이터베이스 상태 (예 : 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답변
총 데이터베이스 크기가 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 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답변
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