-- 전체
select
sum(cast(cast(size as decimal (12,0))* 8192./1024/1024 as int)) as 'SizeMB'
from master.dbo.sysaltfiles
-- 데이터베이스 별
select
name
, cast(cast(size as decimal (12,0))* 8192./1024/1024 as int) as 'SizeMB'
, *
from master.dbo.sysaltfiles
order by 2 desc
-- 데이터베이스 내 테이블
create table #TableSize (
Name varchar(255),
[rows] int,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255))
create table #ConvertedSizes (
Name varchar(255),
[rows] int,
reservedKb int,
dataKb int,
reservedIndexSize int,
reservedUnused int)
EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2),
SUBSTRING(data, 0, LEN(data)-2),
SUBSTRING(index_size, 0, LEN(index_size)-2),
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize
select * from #ConvertedSizes
order by reservedKb desc
drop table #TableSize
drop table #ConvertedSizes
-- 로그 무한증가 하는넘 찾기
select
b.name
, cast(cast(size as decimal (12,0))* 8192./1024/1024 as int) as 'SizeMB'
, log_reuse_wait_desc
, *
from master.dbo.sysaltfiles a
join master.sys.databases b
on a.dbid = b.database_id
where groupid = 0
order by 2 desc
dbcc loginfo ('db')