Manage Learn to apply best practices and optimize your operations.

Forecasting growth

Learn how you can forecast server growth by using SQL Server Agent from within SQL Server in this excerpt from "SQL Server 2000 for experienced DBAs" by Brian Knight.

 SQL Server 2000 for experienced DBAs: Chapter 5, 'Monitoring and Tuning SQL Server' The following excerpt, courtesy of McGraw-Hill Osborne Media, is from Chapter 5 of the book "SQL Server 2000 for experienced DBAs" written by Brian Knight. Click for the complete book excerpt series or purchase the book.

Forecasting Growth

The key to any successful DBA career is the ability to answer the hard questions when you're put on the spot in a meeting. For example, when your boss asks, "Why do we need a new server?" it would be great to be able to quantify the growth pattern. As DBAs, we must always look smarter than we actually are by having lots of great reports to fall back on.

Database Growth Reports

One of the reports to keep close at hand is a database growth report. There are many ways to generate one of these reports, but my preferred method is through a homegrown solution using SQL Server Agent. It's simple and, best of all, it's free. First, create a small auditing database or place the tables in an existing database. Create the following table, called DatabaseSizeReport:

CREATE Table DatabaseSizeReport(
Database_Name Varchar(32),
Database_Size int,
CreateDt datetime)

Schedule the job to execute once a day, week, or month, depending on your needs. Use the following query to accomplish this:

INSERT INTO DatabaseSizeReport EXEC usp_databases

The stored procedure usp_databases can be downloaded from
http://www as an alternative to sp_databases. This query outputs the name and size (in kilobytes) of every database on your system. Divide the figure by 1024 to obtain megabytes, and again by 1024 to view the size in gigabytes. This also works on linked servers (covered in Chapter 9). The resulting table looks like the output in Table 5-2.
Use Excel to analyze the data you've gathered. In Excel, choose Data | Get External Data, and use the following query to gather data from SQL Server:

SELECT Database_Name, Database_Size, CreateDt from DatabaseSizeReport Order by Database_Name

After the data is refreshed in Excel, you can create charts to analyze your database growth rate. Use the same type of process to monitor growth in connections or any

Database_Name Datebase_Size CreateDt
Master 12480 2001-01-21 20:36:38.670
Model 1152 2001-01-21 20:36:38.670
Msdb 14080 2001-01-21 20:36:38.670
Northwind 3712 2001-01-21 20:36:38.670
Pubs 2048 2001-01-21 20:36:38.670
REPORTS 1144 2001-01-21 20:36:38.670
Tempdb 20992 2001-01-21 20:36:38.670
(7 row(s) affected)

Table 5-2 Results of Running the Stored Procedure usp_databases

other type of SQL Server data. You can also use the sysperfinfo table in the master database to gather SQL Server statistical information.

Click for the next excerpt in this series: sqldiag

Click for the complete book excerpt series.

This was last published in July 2005

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.