---------------------------------------------------------------------------------
-- DB SIZE CHECK
---------------------------------------------------------------------------------
-- 작성자 : 김민석
-- 수정자 : 추숙
-- 마지막 수정 내용
-- 1. 수정내용 없음 ㅜ.ㅜ
---------------------------------------------------------------------------------
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_checkDBsize')
BEGIN
DROP PROCEDURE sp_checkDBsize
END
GO
CREATE PROCEDURE dbo.sp_checkDBsize
@dbname sysname = null
AS
SET NOCOUNT ON
-- DB명 존재여부 체크
IF @dbname IS NOT NULL
AND @dbname NOT IN ( SELECT name
FROM sysdatabases )
BEGIN
raiserror('relax!... you''re just one step away from the results. please verify the database name is correct and try again. ', 16, 1)
return (1)
END
if exists (select name from tempdb..sysobjects where name = 'DBsize ' and type = 'u' )
drop table tempdb..DBsize
if exists (select * from tempdb..sysobjects where name = '#sizeinfo' and type = 'u')
drop table #sizeinfo
create table #sizeinfo
(
db_name varchar(30) not null primary key clustered ,
total dec (7, 1) null,
data dec (7, 1) null,
data_used dec (7, 1) null,
[data (%)] dec (7, 1) null,
data_free dec (7, 1) null,
[data_free (%)] dec (7, 1) null,
log dec (7, 1) null,
log_used dec (7, 1) null,
[log (%)] dec (7, 1) null,
log_free dec (7, 1) null,
[log_free (%)] dec (7, 1) null,
status dec (7, 1) null
)
create table tempdb..DBsize
(
db_name varchar(30),
data dec (7, 1) null,
data_used dec (7, 1) null,
[data (%)] dec (7, 1) null,
data_free dec (7, 1) null,
[data_free (%)] dec (7, 1) null,
log dec (7, 1) null,
log_used dec (7, 1) null,
[log (%)] dec (7, 1) null,
log_free dec (7, 1) null,
[log_free (%)] dec (7, 1) null
)
insert #sizeinfo ( db_name, log, [log (%)], status )
exec ('dbcc sqlperf(logspace) with no_infomsgs')
print '' print ''
if @dbname is null
declare dbname cursor for select name from sysdatabases order by name asc
else if @dbname is not null
begin
delete from #sizeinfo where db_name <> @dbname
declare dbname cursor for select name from sysdatabases where name = @dbname
end
open dbname
fetch next from dbname into @dbname
while @@fetch_status = 0
begin -- adding .0 at the end of interger to avoid divide by zero error
exec ( ' use [' + @dbname + '] declare @total dec(7, 1),
@data dec (7, 1),
@data_used dec (7, 1),
@data_percent dec (7, 1),
@data_free dec (7, 1),
@data_free_percent dec (7, 1),
@log dec (7, 1),
@log_used dec (7, 1),
@log_used_percent dec (7, 1),
@log_free dec (7, 1),
@log_free_percent dec (7, 1)
set @total = (select sum(convert(dec(15),size)) from sysfiles) * 8192.0 / 1048576.0
set @data = (select sum(size) from sysfiles where (status & 64 = 0)) * 8192.0 / 1048576.0
set @data_used = (select sum(convert(dec(15),reserved)) from sysindexes where indid in (0, 1, 255)) * 8192.0 / 1048576.0
set @data_percent = (@data_used * 100.0 / @data)
set @data_free = (@data - @data_used)
set @data_free_percent = (@data_free * 100.0 / @data )
set @log = (select log from #sizeinfo where db_name = '''+@dbname+''')
set @log_used_percent = (select [log (%)] from #sizeinfo where db_name = '''+@dbname+''')
set @log_used = @log * @log_used_percent / 100.0
set @log_free = @log - @log_used
set @log_free_percent = @log_free * 100.0 / @log
update #sizeinfo set total = @total,
data = @data ,
data_used = @data_used,
[data (%)] = @data_percent,
data_free = @data_free,
[data_free (%)] = @data_free_percent,
log_used = @log_used,
log_free = @log_free,
[log_free (%)] = @log_free_percent
where db_name = '''+@dbname+'''')
fetch next from dbname into @dbname
end
close dbname
deallocate dbname
print '*** note: all numbers are in mb. if you see unreasonable or negative values, it is time to run dbcc updateusage. ***'
print ''
--insert into tempdb..DBsize (db_name, data, data_used, [data (%)], data_free, [data_free (%)], log, log_used, [log (%)], log_free, [log_free (%)])
select @@servername servername, db_name, data, data_used, [data (%)], data_free, [data_free (%)], log, log_used, [log (%)], log_free, [log_free (%)]
from #sizeinfo --order by db_name asc --compute sum(total)
drop table #sizeinfo
GO
-- TEST QUERY
exec dbo.sp_checkDBsize -- 모든 데이타베이스
exec dbo.sp_checkDBsize 'AdventureWorks' -- 특정 데이타베이스 사이즈를 측정 한다.