Easing the SQL Server Database Capacity Planning Burden

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

Problem

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.

Solution

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
GO

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
begin
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
end

close csrTables
deallocate csrTables

set quoted_identifier on
GO 

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

About the author

MSSQLTips.com was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips