반응형
SQL Server 데이터베이스 크기 선택
데이터베이스 크기만 가져오기 위해 어떻게 SQL 서버를 쿼리할 수 있습니까?
사용했습니다.
use "MY_DB"
exec sp_spaceused
제가 할게요.
database_name database_size unallocated space
My_DB 17899.13 MB 5309.39 MB
필요 없는 여러 열을 반환합니다. 이 저장 프로시저에서 database_size 열을 선택하는 속임수가 있습니까?
저도 이 코드를 시도해 봤습니다.
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name,
(size * 8) / 1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'MY_DB'
결과는 다음과 같습니다.
DatabaseName Logical_Name Physical_Name SizeMB
MY_DB MY_DB D:\MSSQL\Data\MY_DB.mdf 10613
MY_DB MY_DB_log D:\MSSQL\Data\MY_DB.ldf 7286
그래서 이렇게 썼습니다.
SELECT SUM(SizeMB)
FROM (
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name,
(size * 8) / 1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'MY_DB'
) AS TEMP
1183호를 받았습니다.
그래서 그것은 효과가 있지만 이것을 얻을 수 있는 적절한 방법이 있을까요?
한 번 해보세요.
쿼리:
SELECT
database_name = DB_NAME(database_id)
, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db
GROUP BY database_id
출력:
-- my query
name log_size_mb row_size_mb total_size_mb
-------------- ------------ ------------- -------------
xxxxxxxxxxx 512.00 302.81 814.81
-- sp_spaceused
database_name database_size unallocated space
---------------- ------------------ ------------------
xxxxxxxxxxx 814.81 MB 13.04 MB
기능:
ALTER FUNCTION [dbo].[GetDBSize]
(
@db_name NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN
SELECT
database_name = DB_NAME(database_id)
, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID(@db_name)
OR @db_name IS NULL
GROUP BY database_id
2016/01/22 업데이트:
크기, 사용 가능한 공간, 마지막 데이터베이스 백업에 대한 정보 표시
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
, bu.full_last_date
, bu.full_size
, bu.log_last_date
, bu.log_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
LEFT JOIN (
SELECT
database_name
, full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
, full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
, log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
, log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
FROM (
SELECT
s.database_name
, s.[type]
, s.backup_finish_date
, backup_size =
CAST(CASE WHEN s.backup_size = s.compressed_backup_size
THEN s.backup_size
ELSE s.compressed_backup_size
END / 1048576.0 AS DECIMAL(18,2))
, RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
FROM msdb.dbo.backupset s
WHERE s.[type] IN ('D', 'L')
) f
WHERE f.RowNum = 1
GROUP BY f.database_name
) bu ON d.name = bu.database_name
ORDER BY t.total_size DESC
출력:
database_id name state_desc recovery_model_desc total_size data_size data_used_size log_size log_used_size full_last_date full_size log_last_date log_size
----------- -------------------------------- ------------ ------------------- ------------ ----------- --------------- ----------- -------------- ----------------------- ------------ ----------------------- ---------
24 StackOverflow ONLINE SIMPLE 66339.88 65840.00 65102.06 499.88 5.05 NULL NULL NULL NULL
11 AdventureWorks2012 ONLINE SIMPLE 16404.13 15213.00 192.69 1191.13 15.55 2015-11-10 10:51:02.000 44.59 NULL NULL
10 locateme ONLINE SIMPLE 1050.13 591.00 2.94 459.13 6.91 2015-11-06 15:08:34.000 17.25 NULL NULL
8 CL_Documents ONLINE FULL 793.13 334.00 333.69 459.13 12.95 2015-11-06 15:08:31.000 309.22 2015-11-06 13:15:39.000 0.01
1 master ONLINE SIMPLE 554.00 492.06 4.31 61.94 5.20 2015-11-06 15:08:12.000 0.65 NULL NULL
9 Refactoring ONLINE SIMPLE 494.32 366.44 308.88 127.88 34.96 2016-01-05 18:59:10.000 37.53 NULL NULL
3 model ONLINE SIMPLE 349.06 4.06 2.56 345.00 0.97 2015-11-06 15:08:12.000 0.45 NULL NULL
13 sql-format.com ONLINE SIMPLE 216.81 181.38 149.00 35.44 3.06 2015-11-06 15:08:39.000 23.64 NULL NULL
23 users ONLINE FULL 173.25 73.25 3.25 100.00 5.66 2015-11-23 13:15:45.000 0.72 NULL NULL
4 msdb ONLINE SIMPLE 46.44 20.25 19.31 26.19 4.09 2015-11-06 15:08:12.000 2.96 NULL NULL
21 SSISDB ONLINE FULL 45.06 40.00 4.06 5.06 4.84 2014-05-14 18:27:11.000 3.08 NULL NULL
27 tSQLt ONLINE SIMPLE 9.00 5.00 3.06 4.00 0.75 NULL NULL NULL NULL
2 tempdb ONLINE SIMPLE 8.50 8.00 4.50 0.50 1.78 NULL NULL NULL NULL
또한 결과를 다음 쿼리의 결과와 비교합니다.
EXEC sp_helpdb @dbname= 'MSDB'
다음과 유사한 결과를 생성합니다.
SQL Server 데이터베이스 및 데이터베이스 파일의 사용 가능한 공간을 결정하는 다양한 방법에 대한 좋은 기사가 있습니다.
SQL Server 2012에서 SQL 데이터베이스 크기를 계산할 때 완벽하게 작동했습니다.
exec sp_spaceused
여기에는 이미 많은 훌륭한 답변이 있지만 표준 보고서를 사용하여 SQL Server Management Studio(SSMS)를 통해 SQL Server 데이터베이스 크기를 쉽고 빠르게 확인할 수 있는 방법에 대해 언급할 가치가 있습니다.
필요한 보고서 실행하기
- 데이터베이스를 마우스 오른쪽 버튼으로 클릭합니다.
- 보고서 > 표준 보고서 > 디스크 사용으로 이동합니다.
멋진 보고서를 출력합니다.
여기서 Total space Reserved는 디스크에 있는 데이터베이스의 총 크기이며 모든 데이터 파일의 크기와 모든 트랜잭션 로그 파일의 크기를 포함합니다.
후드 아래에서 SSMS는 dbo.sysfiles view 또는 sys.database_files view(MSSQL 버전에 따라 다름)와 이 쿼리의 일부를 사용하여 Total space Reserved 값을 가져옵니다.
SELECT sum((convert(dec (19, 2),
convert(bigint,SIZE))) * 8192 / 1048576.0) db_size_mb
FROM dbo.sysfiles;
SQL Server에서 Azure 및 사내 데이터베이스 크기 확인
방법 1 – 'sys.database_files' 시스템 뷰 사용
SELECT
DB_NAME() AS [database_name],
CONCAT(CAST(SUM(
CAST( (size * 8.0/1024) AS DECIMAL(15,2) )
) AS VARCHAR(20)),' MB') AS [database_size]
FROM sys.database_files;
방법 2 – 'sp_space used' 시스템 저장 프로시저 사용
EXEC sp_spaceused ;
SELECT sys.databases.name AS [Database Name],
CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Size]
FROM sys.databases
JOIN sys.master_files
ON sys.databases.database_id=sys.master_files.database_id
GROUP BY sys.databases.name
ORDER BY sys.databases.name
EXEC sp_spaceused @oneresultset = 1모든 결과를 하나의 행에 표시
'EXEC sp_space used'만 실행하면 SQL Server Management Studio v17.9에서 작동하는 두 행이 표시됩니다.
단순히 단일 데이터베이스 크기를 확인하고자 하는 경우 SSMS Gui를 사용하여 확인할 수 있습니다.
서버 탐색기로 이동 -> 확장 -> 데이터베이스를 마우스 오른쪽 버튼으로 클릭 -> 속성 선택 -> 팝업 창에서 일반 탭 -> 크기 참조
Source: Sql server에서 데이터베이스 크기 확인(다양한 방법 설명)
이 링크에 이어 이 쿼리가 작동하는 방식을 확인할 수 있습니다.
IF OBJECT_ID('tempdb..#spacetable') IS NOT NULL
DROP TABLE tempdb..#spacetable
create table #spacetable
(
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 ''divide by zero'' 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 ''divide by zero'' 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
drop table #spacetable
SELECT
DB_NAME (database_id) as [Database Name],
name as [Database File Name],
[Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END,
size*8/1024 as 'Size (MB)',
physical_name as [Database_File_Location]
FROM sys.master_files
ORDER BY 1,3
산출량
Database Name Database File Name Type Size (MB) Database_File_Location
--------------------------- ------------------------------- ------------------- ----------- ---------------------------------------------------------------
AdventureWorksDW2017 AdventureWorksDW2017 Data File(s) 136 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2017.mdf
AdventureWorksDW2017 AdventureWorksDW2017_log Log File(s) 72 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2017_log.ldf
DBA_Admin DBA_Admin Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\DBA_Admin.mdf
DBA_Admin DBA_Admin_log Log File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\DBA_Admin_log.ldf
EventNotifications EventNotifications Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\EventNotifications.mdf
EventNotifications EventNotifications_log Log File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\EventNotifications_log.ldf
master master Data File(s) 4 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf
master mastlog Log File(s) 2 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
model modeldev Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\model.mdf
model modellog Log File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\modellog.ldf
msdb MSDBData Data File(s) 19 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf
msdb MSDBLog Log File(s) 13 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf
tempdb temp2 Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf
tempdb temp3 Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf
tempdb temp4 Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf
tempdb tempdev Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
tempdb templog Log File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf
코드 아래에서 시도합니다.그것의 매우 좋은 코드:
with CteDbSizes
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
dbFileSizes.[name] AS DatabaseName,
(select sum(size) from CteDbSizes where type = 1 and CteDbSizes.database_id = dbFileSizes.database_id) LogFileSizeMB,
(select sum(size) from CteDbSizes where type = 0 and CteDbSizes.database_id = dbFileSizes.database_id) DataFileSizeMB
from sys.databases dbFileSizes ORDER BY DataFileSizeMB desc
실제 데이터의 크기를 가져오려면 '온라인' 데이터 파일만 필터링하면 됩니다.그렇지 않으면 GUI에 표시된 크기와 상당히 다른 크기를 얻을 수 있습니다.일부 ghost | old 가비지 레코드는 sys.master_files에 있을 수 있습니다. 예를 들어 'DEFUMENT' 데이터 파일입니다.
with fs
as
(
select database_id, type, size * 8.0 / 1024 size, physical_name
from sys.master_files
where state_desc = 'ONLINE' -- ONLINE,RESTORING,RECOVERING,RECOVERY_PENDING,SUSPECT,,OFFLINE, DEFUNCT
)
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,
(select sum(size) from fs where fs.database_id = db.database_id) TotalFileSizeMB
from sys.databases db
--order by database_id
order by TotalFileSizeMB desc
CREATE procedure SP_DBSIZE
AS
IF (OBJECT_ID('db_size','U')) IS NOT NULL
drop table DB_size
create table db_size(name nvarchar(100), db_size nvarchar(100), owner nvarchar(100), dbid int, created nvarchar(30), status nvarchar(255), compatibility_level nvarchar(10))
insert into db_size
exec sp_helpdb
update db_size set status = left(status,50)
alter table db_size alter column status nvarchar(50)
select * from db_size
언급URL : https://stackoverflow.com/questions/18014392/select-sql-server-database-size
반응형
'source' 카테고리의 다른 글
| Postgre로 SQL 덤프 가져오기SQL 데이터베이스 (0) | 2023.05.06 |
|---|---|
| Git 하위 모듈에서 변경사항을 "커밋"하려면 어떻게 해야 합니까? (0) | 2023.05.06 |
| iis 7.0에서 .net Framework 4.0 웹 사이트를 실행하는 데 문제가 있습니다. (0) | 2023.05.06 |
| 왜 "bytes(n)"는 n을 이진 표현으로 변환하는 대신 길이 n 바이트 문자열을 생성합니까? (0) | 2023.05.06 |
| 모든 키 누르기에서 Angular 2 변경 이벤트 (0) | 2023.05.06 |