HOW-TO: Track and Report MOM (and Other SQL Database) Growth : Part 1

 

Ever wake up one day to discover your Onepoint, SystemCenterReporting or other SQL database has grown unexpectedly?

Have you ever been asked to estimate future growth of SQL databases in your environment?

 

If the answer to either of the above, then check this out. A colleague sent me a script the other day makes the task of monitoring and reporting the growth trends of SQL databases an easy task. The following SQL script creates a stored procedure for generating usage data, and a table in the master database for storing this data (script is also well-commented if you’re interested in reading how it collects this data). In the 1st installment, we'll actually configure the components for collecting usage data. Then in the 2nd installment, we'll take advantage of our MOM Reporting Server by creating some simple trend reports in SQL Reporting Services to graphically display the data we’ve collected.

 

Run the script

 

Start by running the following script in SQL Query Analyzer on the MOM Database Server or other target SQL Server.

http://www.it-jedi.net/scripts/dbsizewatch.htm

 

  1. On the MOM database or other target SQL Server, launch SQL Query Analyzer.
  2. Paste the SQL script from the URL above into the query window. Press F5 or click the green run arrow to run the script.
  3. Assuming the script runs successfully, check for the existence of
    1. A table named DB_SizeWatch in the master database. (In Enterprise Mgr, expand Databases, Master, Tables)
    2. A stored procedure named usp_DBSizeWatch. (In Enterprise Mgr, expand Databases, Master, Stored Procedures)

 

Validate components and functionality

 

Once you’ve verified both the stored procedure and the table are present, run the stored procedure to populate the table.

  1. In Query Analyzer, type execute usp_DBSizeWatch, then press F5 or click the green arrow.
  2. Run the following query to display results of the first run:

 

The table reports 1) database and transaction log space size, 2) space used and 3) space available.

 

Figure 1 – Table output

 

 

Create SQL job to execute on a schedule

 

Now it’s time to schedule the daily run of the stored procedure to log the data. (I actually modified this script a bit to include a timestamp so the script could be run multiple times daily if necessary).

 

  1. In SQL Query Analyzer, expand Management, SQL Server Agent, and highlight Jobs.
  2. Right click the Jobs node, and from the jobs menu, click New Job.
  3. Configure the job with the following settings:

 

Table 1 - Job Properties:

 

Tab

Setting

General

Enter a job name, owner (should be a local administrator), and a description. Default target of local server will suffice.

Steps

General tab:Click the New button. Enter step name ‘Gather DB Stats’. In the command field, enter execute usp_DBSizeWatch

Advanced tab: accept default values.

Schedule

Click the New Schedule button. Enter schedule name ‘Daily Run’.

Select Recurring radio button, then click Change, and set desired schedule time and frequency.

Notification

Select checkbox labeled ‘Write to Windows application event log’. This will generate an event on job failure that will be trigger alerts from MOM SQL Mgmt Pack rules.

 

Figure 2 – SQL Job Properties