Monitor Database Growth on Multiple SQL Servers

Wednesday Apr 14th 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

Learn how to monitor the growth and size of all the databases on all of the SQL Servers by finding the size of a database's physical files and periodically storing the results in a table.

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"

Step 3 Execute the VB Script

When executing the VBScript, it stores all the information about the .mdf, .ndf and .ldf files and the Disk size of all the servers on two tables, namely "DBGrowth" and "DiskSpace," in the database "DBMonitor." After executing the VBScript, it prompts you with a message like the oe below. When you are scheduling this VBScript, remove the code for displaying this message box.

Note: Make sure the NT login that you use for running this VBScript has enough permission to access all of the SQL Servers listed in the table "Servers." When you schedule this VBScript, make sure the account that you are using has enough permission to access all of the SQL Servers.

Step 4 Query the Database tables

The query below returns information about Disk Space on all the SQL Servers:

SQL

Use DBMonitor
Go
select * from Diskspace order by timestamps
go

Results

YUKON

3/15/04 7:14 PM

C

15058

YUKON

3/15/04 7:14 PM

D

17101

SQL

3/15/04 7:16 PM

C

1492

SQL

3/15/04 7:16 PM

D

4351

SQL

3/15/04 7:16 PM

F

45382

SQL

3/15/04 7:16 PM

G

43922

ETL

3/15/04 7:16 PM

D

4946

ETL

3/15/04 7:16 PM

C

1039

YUKON

3/16/04 3:00 AM

C

15058

YUKON

3/16/04 3:00 AM

D

17101

SQL

3/16/04 3:02 AM

D

4351

SQL

3/16/04 3:02 AM

F

45042

SQL

3/16/04 3:02 AM

G

43922

SQL

3/16/04 3:02 AM

C

1448

ETL

3/16/04 3:02 AM

C

1039

ETL

3/16/04 3:02 AM

D

4946

SQL

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

Results

YUKON

AdventureWorks

8448

3/15/04 7:14 PM

C:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\DATA\
AdventureWorks_data.mdf

YUKON

AdventureWorks

96

3/15/04 7:14 PM

C:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\DATA\
AdventureWorks_log.ldf

SQL

Bank

88

3/15/04 7:16 PM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\Bank.mdf

SQL

Bank

63

3/15/04 7:16 PM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\Bank_log.LDF

SQL

DBMonitor

80

3/15/04 7:16 PM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\DBMonitor.mdf

SQL

DBMonitor

63

3/15/04 7:16 PM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\DBMonitor_log.LDF

ETL

analyse

80

3/15/04 7:16 PM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\analyse.mdf

ETL

analyse

63

3/15/04 7:16 PM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\analyse_log.LDF

ETL

Journal

63

3/15/04 7:16 PM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\Journal_log.LDF

ETL

Journal

80

3/15/04 7:16 PM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\Journal.mdf

ETL

Walker

80

3/15/04 7:16 PM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\Walker.mdf

ETL

Walker

63

3/15/04 7:16 PM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\Walker_log.LDF

YUKON

AdventureWorks

8448

3/16/04 3:00 AM

C:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\DATA\
AdventureWorks_data.mdf

YUKON

AdventureWorks

96

3/16/04 3:00 AM

C:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\DATA\
AdventureWorks_log.ldf

SQL

Bank

88

3/16/04 3:02 AM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\Bank.mdf

SQL

Bank

63

3/16/04 3:02 AM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\Bank_log.LDF

SQL

DBMonitor

63

3/16/04 3:02 AM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\DBMonitor_log.LDF

SQL

DBMonitor

112

3/16/04 3:02 AM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\DBMonitor.mdf

ETL

analyse

80

3/16/04 3:02 AM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\analyse.mdf

ETL

analyse

63

3/16/04 3:02 AM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\analyse_log.LDF

ETL

Walker

80

3/16/04 3:02 AM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\Walker.mdf

ETL

Walker

63

3/16/04 3:02 AM

D:\Program Files\Microsoft SQL Server\
MSSQL\data\Walker_log.LDF

Step 5 Analyze the data in tables

Collect size information over a period of time and then analyze the data for Disk size changes and Database growth by creating a cross-tab query as shown below.

SQL

--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

Servername

Databasename

3/15/2004

3/16/2004

YUKON

AdventureWorks

9824

9825

YUKON

AdventureWorksDW

7743

7749

ETL

analyse

143

142

SQL

ArrowPack

768

868

SQL

Bank

151

156

ETL

CM_Sales

4816

4818

--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

How does this work?

Basically, the VBScript Creates two tables, "DBGrowth" and "DiskSpace," in the database "tempdb" of all the servers and runs the two queries shown below and then stores the output in those tables. Then it queries those two tables from "tempdb" and stores the results in the "DBGrowth" and "DiskSpace" tables of the database "DBMonitor." Finally, the VBScript deletes the tables "DBGrowth" and "DiskSpace" from the database "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

Conclusion

As I mentioned before, the main intent of this article is to give a basic idea of how to find the size of the database physical files and periodically store the results in a table, so that you can monitor the growth and size of all the databases on all the SQL Servers. You can also create a small web page to display the growth of the database graphically using the data stored in these tables so that you can see the rate of growth online.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved