용량조사

-- 전체 
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


use db
go

DBCC SHOWFILESTATS 
go

-- 데이터베이스 내 테이블
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')