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.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.
| Database_Name | Database_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) |
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 other type of SQL Server data. You can also use the sysperfinfo table in the master database to gather SQL Server statistical information.
|
|
