Monitor Database Growth on Multiple SQL Servers

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”