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.
Click here for code example 1.
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.
Click here for code example 2.
Click here for code example 3.
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) DESCHopefully, 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!
