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
» 
IE 7
Firefox 2.0
Comment and Contribute
