One of the many responsibilities of the DBA is to track and report database space utilization. In addition to knowing space utilization today, trending analysis is required to fully understand just how fast databases are growing. It is important to know not only the total database size at a certain point in time, but also total space utilized and total free space. This requires that a periodic snapshot be taken of the space used by databases.
This task is fairly easy if you have 5 or 10 databases. Just go to Enterprise Manager and jot down the size and space available for each database, then plug it into a spreadsheet. But what if you are responsible for dozens of servers and literally hundreds of databases? All of a sudden, that 'fairly easy' task becomes monumental. Although there are tools available for purchase which can do this for you, it is cheaper to develop a "home grown" solution. This article explains how to create a centralized, automated database space utilization repository using stored procedures, linked servers and scheduled jobs.
This solution was born out of necessity. I am responsible for updating the Data Management section of my company's IT Operating Plan every quarter. In this plan, I provide current statistics and trends regarding the database environment for the company. It was taking around 4 hours to compile this information and plug it into a spreadsheet. After doing this a couple of times, the inevitable statement popped in my head...There's got to be a better way! So, I came up with a solution that would cut that process to about 15 minutes.
NOTE: This process will not work on SQL 6.5 servers, since there is no sysfiles table in the master database. The process has been tested on both SQL 7.0 and 2000.
The first thing I did was look at the sp_spaceused procedure and modify it to suit my needs. This system stored procedure contains the formulas which are used to calculate database size, so I used those instead of reinventing the wheel. The new procedure, sp_dbspace, is shown below. It should be created in the master database on every SQL Server you want to track. All numeric results are in megabytes.
NOTE: The DBCC UPDATEUSAGE statement may take a long time to run on large databases. Although running this command will assure more accurate results, it is not absolutely required. You may want to remark it out if your database is larger than about 30GB.
Next, choose one SQL Server to act as the data collector. On this server, create linked server connections back to the servers being monitored. Linked servers can be created through Enterprise Manager under the Security folder, or through the sp_addlinkedserver stored procedure. Don't worry about creating a linked server back to the data collector. There should already be a self-referencing definition in the master..sysservers table. You can learn more about setting up linked servers in Books Online.
After creating the linked servers on the data collector server, create the spt_dbspace table sp_dbspaceall procedure in the master database on the data collector, as shown below.
Now, all that's left to do is create a SQL Server Agent job to run the sp_dbspaceall stored procedure from the data collector server at regular intervals. I chose the first Sunday of each month at 7am. This is the only job which needs to be set up. The data collector server will query the linked servers and return the results to the spt_dbspace table on the data collector. By manipulating the snapshotdate column, reports can be generated through SQL queries. Below is a sample query which gives a summary of space utilized by server, sorted by total database size, for June of 2002. Note that if there were more than one snapshot taken in June, the results would be skewed. Make sure you group your snapshots properly so that you get the proper results!
select servername, sum(dbsize) as "Total Size", sum(dbreserved) as "Total Used", sum(dbfree) as "Total Free", snapshotdate as "Date" from spt_dbspace where datepart(m,snapshotdate)=6 and datepart(yyyy,snapshotdate)=2002 group by servername, snapshotdate order by sum(dbsize) DESC
Hopefully, this will make the DBA task of trend analysis a little easier for you and will help you to justify the need for a new SAN! Happy Trending!