SQL Server 데이터베이스별 사용량 확인

CREATE procedure [dbo].[db_space]
as

begin
    declare @sql varchar(5000);

    set @sql = 'use [?];declare @id int, @type character(2), @pages bigint, @dbname sysname, @dbsize bigint, @logsize bigint, @reservedpages bigint, @usedpages  bigint, @rowCount bigint;
    select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)), @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from dbo.sysfiles;
    select @reservedpages = sum(a.total_pages),    @usedpages = sum(a.used_pages),    @pages = sum(CASE When it.internal_type IN (202,204) Then 0    When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0    END    ) from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id select  d_name = db_name(), d_used = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2)), d_free = ltrim(str((case when @dbsize >= @reservedpages then (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 / 1048576 else 0 end),15,2));'

    if object_id('tempdb..##db_space_info') is not null
        drop table ##db_space_info

    create table ##db_space_info (
        d_name varchar(50)
        ,d_used varchar(50)
        ,d_free varchar(50)
    )
    insert into ##db_space_info exec sp_msforeachdb @sql;

    select * from ##db_space_info ORDER BY d_name;
end

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 항목은 *(으)로 표시합니다