Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 14, 2004

Monitor Database Growth on Multiple SQL Servers - Page 2

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date