Monitor Database Growth on Multiple SQL Servers

April 14, 2004

It is very important for SQL Server Database Administrators to monitor the database growth on all of the SQL Server boxes. By monitoring such growth, the database administrator can take pro-active actions such as increasing the volumes, moving the .mdf, .ndf and .ldf to different locations and estimating the growth of the database so that the database size can be expanded on time.

The main intent of this article is to give a basic idea of how to find the size of the database physical files and store it in a table periodically, so that you can monitor the growth and size of all the databases on all of the SQL Servers. In addition, it helps in monitoring the disk space on all of the SQL Servers.

Step1 Create Database

In order to monitor the database growth we need a database and some tables to store all of the information. Use the SQL code given below, (or download it from the link), to create the database and the corresponding tables to monitor the database size and growth. Make sure you add all of the SQL server names in the table "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

Step 2 Create VB Script

Create a folder "C:\DBMonitor" and copy and paste (or download from the link) the below VB Script as C:\DBMonitor\DBMonitor.vbs

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"








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers