Monitor Database Growth on Multiple SQL Servers
By Muthusamy Anantha Kumar aka The MAK

번역 : 김종균

담당하는 모든 SQL서버의 데이터증가량을 모니터링하는것을 SQL서버 관리자들에겐
매우중요한 일이다. 용량증가같은 모니터링을 통하여, 데이터베이스 관리자는 디스크 볼륨
확장, 다른 위치로의 .mdf, .ndf 파일이동, 데이터베이스의 증가 예측등과 같은 적절한 선행조치를
취할수 있을것이며 이를 통하여 데이터베이스 사이즈는 적당한 때에 확장되어 질것이다.

이 글의 주목적은 주기적으로 물리적 데이터베이스 파일의 사이즈를 테이블로 저장할것인가에 대한
기본적 생각을 제시하기위함이다. 이로써 모든 SQL서버들의 모든 데이터베이스의 사이즈와 증가량을
모니터 할수 있을껏이다. 또한 SQL서버들의 디스크 사이즈도 모니터링 할수 있을 것이다.

단계1. Create Database
데이터베이스의 증가치를 모니터하기위해 우리는 하나의 데이터베이스와
정보를 저장하기위한 몇몇 테이블이 필요하다.
아래에 제공된 SQL 코드를 사용하여 모니터링에 필요한 데이터베이스와 적절한 테이블들을 생성하고
모든 SQL서버의 이름이 'Servers'테이블에 저장되었는지 확인하라.

-----SQL Code----------------
Use master
Go
Create Database DBMonitor
go
use DBMonitor
go
sp_addlogin 'DBmonitoruser','monitor','DBMonitor'
go
sp_adduser 'DBMonitoruser'
go
sp_Addrolemember 'db_datawriter','DBMonitoruser'
go
sp_Addrolemember 'db_datareader','DBMonitoruser'
go
Create table Servers (id int identity (1,1), Servername varchar(256))
go
insert into servers (Servername) select 'SQL'
insert into servers (Servername) select 'YUKON'
insert into servers (Servername) select 'ETL'
go
CREATE TABLE DBGrowth (
[Servername] [varchar](128),
[Databasename] [varchar](128),
[timestamps] [datetime],
[fileid] [smallint] NOT NULL ,
[groupid] [smallint] NOT NULL ,
[size] [int] NOT NULL ,
[maxsize] [int] NOT NULL ,
[growth] [int] NOT NULL ,
[status] [int] NOT NULL ,
[perf] [int] NOT NULL ,
[name] [nchar] (128) ,
[filename] [nchar] (260) 
)
go
Create table DiskSpace ([Servername] [varchar] (128),
[timestamps] [datetime],
[Drive] [varchar] (2),
[MBFree] [int])
go

단계2. Create VB SCript
'c:\DBMonitor' 라는 폴더를 만들고 아래의 VB스크립트파일을 그 폴더로 복사하자.

-----VB Script Code------------------------
'Author: MAK
'Contact: mak_999@yahoo.com
'Objective: To monitor Database Size and Growth of all SQL Server databases
Dim AdCn
Dim AdRec
Dim i, SQL
Set AdCn = CreateObject("ADODB.Connection")
Set AdRec = CreateObject("ADODB.Recordset")
Set AdCn2 = CreateObject("ADODB.Connection")
Set AdRec2 = CreateObject("ADODB.Recordset")
'Change the below connection parameters according to your environment.
AdCn.Open = "Provider=SQLOLEDB.1;Data
  Source= SQL;Initial Catalog=DBMonitor;user
  id = 'dbmonitoruser';password='monitor' "
SQL1 = "Select ServerName from Servers"
AdRec.Open SQL1, AdCn,1,1
while not Adrec.EOF
computername=Adrec("ServerName")
'msgbox computername
connectionstring="Provider=SQLOLEDB.1;
  Data Source= " +computername+";
  Initial Catalog=master;
  Integrated Security=SSPI"
AdCn2.open= connectionstring
'msgbox connectionstring
SQL2="Begin CREATE TABLE tempdb.dbo.DbGrowth
  ([Servername] [varchar](128),
  [Databasename] [varchar](128),
  [timestamps] [datetime],
  [fileid] [smallint] NOT NULL ,
  [groupid] [smallint] NOT NULL ,
  [size] [int] NOT NULL ,
  [maxsize] [int] NOT NULL ,
  [growth] [int] NOT NULL ,
  [status] [int] NOT NULL ,
  [perf] [int] NOT NULL ,
  [name] [nchar] (128) ,
  [filename] [nchar] (260)   )
END BEGIN exec master.dbo.sp_msforeachdb
  $insert tempdb.dbo.DbGrowth
  select @@servername as Servername,'?' as Databasename,
  getdate() as TimeStamps, * from ?.dbo.sysfiles$
END begin Create table tempdb.dbo.DiskSpace
  (Servername varchar(128),
  timestamps datetime,
  Drive varchar(2),
  MBFree int)
end begin insert tempdb.dbo.diskspace (Drive,MBFree)
  exec master.dbo.xp_fixeddrives
end begin update tempdb.dbo.diskspace set
  servername = @@servername, timestamps =getdate() end"
SQL2=replace(SQL2,"$",chr(34))
'msgbox SQL2
rec=0
'AdCn2.execute SQL2, rec, adCmdtext
AdCn2.execute SQL2
Adrec2.open "Select * from tempdb.dbo.DiskSpace",Adcn2,1,1

while not Adrec2.EOF
sqlx2 ="insert into diskspace(Servername,timestamps,Drive,MBFree)
  values('" & Adrec2("Servername") & "',
  '" & Adrec2("timestamps")  & "',
  '" & Adrec2("Drive") & "',
  " &  Adrec2("MBFree") & ")"
'msgbox   sqlx2

'AdCn.execute SQLx2, rec, adCmdtext
AdCn.execute SQLx2

Adrec2.movenext
wend
Adrec2.close

Adrec2.open "Select * from tempdb.dbo.DBgrowth",Adcn2,1,1

while not Adrec2.EOF
sqlx ="insert into dbgrowth
  (Servername,Databasename,timestamps,fileid,groupid,size,maxsize,growth,status,perf,name,filename)
  values ('" & Adrec2("Servername") & "',
    '" & Adrec2("Databasename")  & "',
 '"  & Adrec2("timestamps")  &  "',
 " & Adrec2("fileid") & ",
 " & Adrec2("groupid") & ",
 "  & Adrec2("size") & ",
 " & Adrec2("maxsize") & ",
 " & Adrec2("growth") & ",
 " & Adrec2("status") & ",
 " & Adrec2("perf") & ",
 '" & Adrec2("name") & "',
 '" & Adrec2("filename") & "')"
'msgbox   sqlx
'AdCn.execute SQLx, rec, adCmdtext
AdCn.execute SQLx
Adrec2.movenext
wend
Adrec2.close
AdCn2.execute "drop table tempdb.dbo.DbGrowth"
AdCn2.execute "drop table tempdb.dbo.DiskSpace"
AdCn2.close

Adrec.movenext
wend

' Remove the code for message box if you are scheduling this vbs
msgbox "DB Monitor Completed"

단계3. Execute the VB Script
위 단계에서 작성된 VB스크립트가 실행되면, 모든 서버의 디스크사이즈 정보와, .mdf, .ndf, .ldf 파일들의
모든 정보가 'DBMonitor'데이터베이스의 'DBGrowth'와 'DiskSpace'테이블에 저장된다.
VB스크립트가 수행되고 나면 'DB Monitor Completed'라는 메세지 박스가 뜨는데, 스케쥴링 작업에 이 스크립트를
사용하려면 메세지 박스를 띄우는 부분의 코드는 빼길바란다.
Note. 이 VB스크립트를 사용하기 위해서 'Servers'테이블에 저장된 모든 SQL서버들에 접근할수 있는 충분한 권한이 있는지
NT login정보를 꼭 확인해야만 한다. 이 VB스크립트를 스케줄링 할때도 모든 SQL서버들에 액세스할 충분한 권한이 있는 계정인지
확인해야한다.

단계4. Query the Database tables
아래의 쿼리는 모든 SQL서버들의 디스크공간에 관한 정보를 나타낼것이다.

Use DBMonitor
Go
select * from Diskspace order by timestamps
go

Results

Use DBMonitor
Go
select Servername, Databasename, size, timestamps,
  filename from DBGrowth order by timestamps, servername, Databasename

Results

단계5. Analyze the data in tables
일정기간동안의 사이즈정보를 수집하여 디스크사이즈의 변화와 크로스탭 쿼리 생성을 통한
데이터베이스 증가치의 데이터를 분석하라.

--Daily Growth
Select ServerName,DatabaseName,
 SUM(CASE WHEN convert(varchar(12),timestamps,110) = '03-15-2004'
   THEN Size ELSE 0 END) AS '03-15-2004' ,
 SUM(CASE WHEN convert(varchar(12),timestamps,110) = '03-16-2004'
   THEN Size ELSE 0 END) AS '03-16-2004'
from DBGrowth group by ServerName,DatabaseName

Results

--Average Growth for every week
Select ServerName,DatabaseName,
 SUM(CASE WHEN datepart(wk,timestamps) = 1 THEN Size ELSE 0 END)/7 AS 'Week1' ,
 SUM(CASE WHEN datepart(wk,timestamps) = 2 THEN Size ELSE 0 END)/7 AS 'Week2' ,
 SUM(CASE WHEN datepart(wk,timestamps) = 3 THEN Size ELSE 0 END)/7 AS 'Week3'
from DBGrowth group by ServerName,DatabaseName

-- Average Growth for every year
Select ServerName,DatabaseName,
 SUM(CASE WHEN year(timestamps) = 2001 THEN Size ELSE 0 END)/365 AS '2001' ,
 SUM(CASE WHEN year(timestamps) = 2002 THEN Size ELSE 0 END)/365 AS '2002' ,
 SUM(CASE WHEN Year(timestamps) = 2003 THEN Size ELSE 0 END)/365 AS '2003' ,
 SUM(CASE WHEN Year(timestamps) = 2004 THEN Size ELSE 0 END)/365 AS '2004'
from DBGrowth group by ServerName,DatabaseName

어떻게 수행되는가 ?
기본적으로, 그 VB스크립트는 모든 SQL서버의 'tempdb'에 'DBGrowth'와'DiskSpace'를 생성한다.
그리고 아래표시된 두개의 쿼리가  수행되면 그 임시 테이블들에 결과값이 저장되어진다. 그런다음
 두개의 임시테이블을 쿼리하여 그 결과값을 'DBMonitor' 데이터베이스의 'DBGrowth'와 'DiskSpace'테이블에
저장한다. 그리고 끝으로 'tempdb'에 생성된 두개의 테이블은 삭제된다.

Exec master.dbo.sp_msforeachdb
  "insert tempdb.dbo.DbGrowth select @@servername as Servername,
  '?' as Databasename,getdate() as TimeStamps, * from ?.dbo.sysfiles"

Exec master.dbo.xp_fixeddrives

결론
처음에 언급했듯이, 이번 글의 주목적은 데이터베이스의 물리적 파일사이즈를 어떻게 찾고 주기적으로 저장할지에 관한
기본적인 아이디어를 제공하기 위함이다. 이를 통해 당신은 관리하는 모든 SQL서버들의 데이터베이스의 크기와 증가량을
모니터 할수 있을것이며, 테이블에 저장된 모니터정보를 활용하여 간단한 웹페이지 작성할수 도 있을것이며 그래픽하게 표시된
 데이터베이스 증가치를 온라인으로 볼 수도 있을것이다.