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.
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(
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
.sqlservercentral.com/experienceddba/ 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
|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 first published in July 2005