admin.sp_checkDBsize DB SIZE check

---------------------------------------------------------------------------------
-- 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' -- 특정 데이타베이스 사이즈를 측정 한다.