The second step of Get DBSTATS, which Figure 4 shows, executes the statements that usp_get_dbstats generated, extracting and saving disk-space usage information. Figure 4 shows the osql command that executes the script that the first step produced. The input (-i) parameter feeds into the osql process the T-SQL script that the first step built.
In my shop, I've scheduled the SQL Server Agent job to run once a week so that I can capture the database space-usage statistics and monitor the growth of our databases week by week. You need to determine how frequently you should gather space-usage statistics for your environment. Capturing disk-space usage lets me perform several kinds of disk-space usage analysis. I can track monthly and yearly disk usage, both by individual databases and overall, and how much additional disk space was used when we migrated data related to a particular project.
Growth-Rate Calculation
If you don't have any disk-space usage information, predicting an average database growth rate is extremely difficult. After you've implemented a disk-usage collection method such as the one I've outlined, you have statistics available to help you calculate a database's average growth rate. I produce a simple Microsoft Excel chart monthly that tracks our disk-space usage over time.
Figure 5 shows the monthly disk-space usage for one of our production servers, SQLPROD1. This graph represents the amount of disk space that all our production databases on SQLPROD1 were using on different dates over a period of 7 months. Note that I recorded several spikes in the graph. Over time, I can associate the peaks and valleys with specific events that cause unusual growth in our database, so I can better predict growth rates for upcoming database work. In Figure 5, you can see when we added DB_TEST: The used space on server SQLPROD1 grew almost 3GB.
Although this graph represents disk-space usage statistics starting only in July 2001, getting a picture of the average disk-space growth rate for a more recent or longer period on this server is easy. I can determine the monthly growth rate by using the following simple formula:
MONTHLY_GROWTH_RATE = (SPACE_USED_END
- SPACE_USED_BEGIN) / NUMBER_OF_MONTHS
The amount of disk space occupied on July 1, 2001 (SPACE_USED_BEGIN), was 6.5GB. By February 4, 2002 (SPACE_USED_END), the used disk space had grown to 7.66GB. The number of months between the July and February data points is a little more than 7. According to this formula, the monthly growth rate for our SQLPROD1 box is a little more than 0.16GB per month. Now that I can calculate the monthly growth rate, based on statistics, I can predict the number of months before our database growth consumes our available free disk space and I'll have time to acquire more disk space in advance.
Calculating a monthly growth rate for our SQLPROD1 server would be impossible without collecting statistics over time. This homegrown solution, using documented and undocumented DBCC statements, meets my organization's needs. Other organizations might find they need to collect more historical space-usage information, such as space usage by tables within a database. Whether you acquire canned software to track space usage or choose a homegrown solution, gathering database-growth information over time can give you valuable insight into the growth patterns of your databases. Without historical growth-rate information, you have no way to adequately understand a database's disk usage. Knowing the current growth rate of each database will help you more accurately plan for future disk acquisitions.