Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Easing the SQL Server Database Capacity Planning Burden

By:   |   Updated: 2006-11-30   |   Comments   |   Related: More > Database Administration


A common headache for database and system administrators is capacity planning in SQL Server 2000. A good guess is a good start, but it is equally important to keep a close eye on databases and tables to make sure disk space shortage doesn't creep up on you. One of the easiest ways to keep up with your databases is to use some of the system functions to your advantage, like sp_spaceused and xp_fixeddrives.


The first step, if not already done in your organization, is to create a database designed to house various information. In case we chose to name the database DataAdmin. To get an idea of the field sizes, we ran a query on the sysobjects table of AdventureWorks2000 to see how long the table names are:

select max(len(name)) from adventureworks2000..sysobjects (nolock) where xtype='U' 

Since the max length was 38, we'll create a table named "tblTableSizes" with a field length of 50 characters. In addition, entries into the table will be based on the sp_spaceused stored procedure and other statistics, so we'll need the following columns:

create table DataAdmin..tblTableSizes
TableEntryID int identity(1,1) primary key,
EntryDate smalldatetime not null default getdate(),
TableName varchar(50),
Row_Count int,
ReservedSize varchar(20),
DataSize varchar(20),
IndexSize varchar(20),
UnusedSize varchar(20),
CreationDate smalldatetime )

Now that we have our table created, we can write the procedure to populate the table. In order to gather all the information we create a cursor that gathers all the user tables. In SQL Server 2000, we grab the creation date from the sysobjects table (column crdate) and then run sp_spaceused to insert the information into the table. After the information is inserted into the table, we update the table with the creation date:

set quoted_identifier off

Use AdventureWorks2000
declare @tablename varchar(50), @statement nvarchar(300), @creationdate smalldatetime

declare csrTables cursor for select top 100 percent [name] from AdventureWorks2000..sysobjects (nolock) where
xtype = 'U' and left(name, 3) != 'dt_'
open csrTables
fetch next from csrTables into @tablename
while @@fetch_status = 0
set @statement = "insert DataAdmin..tblTableSizes(TableName, Row_Count, ReservedSize,
DataSize, IndexSize, UnusedSize) exec sp_spaceused '" + @tablename + "'"
set @CreationDate = (select crdate from AdventureWorks2000..sysobjects (nolock) where [name] = @tablename)
exec sp_executesql @statement
update DataAdmin..tblTableSizes set CreationDate = @CreationDate where TableName = @TableName
fetch next from csrTables into @tablename

close csrTables
deallocate csrTables

set quoted_identifier on

Another example that can also be beneficial is using the xp_fixeddrives extended stored procedure. We created a table in the DataAdmin database named tblDiskInfo with the following columns:

create table DataAdmin..tblDiskInfo
entryid int identity(1,1) primary key,
entrydate smalldatetime not null default getdate(),
servername varchar(50),
driveletter char(1),
MBFree int

Once the table is created we run the following statements to insert information into the table:

insert DataAdmin..tblDiskInfo(driveletter, mbfree) exec master..xp_fixeddrives
update DataAdmin..tblDiskInfo set servername = @@servername

Having this information in a central location makes it easier to review and report on. In addition, if you have multiple servers, use can SQL Agent Jobs or Data Transformation Services to transfer the information to a central repository, eliminating the need for visiting each SQL Server 2000 instance every day for the information.

Next Steps
  • Review your current policy on capacity planning and how you gather information to justify resource expenditures
  • Review information on sp_spaceused

Last Updated: 2006-11-30

get scripts

next tip button

About the author
MSSQLTips author Edgewood Solutions Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools