Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Easing the SQL Server Database Capacity Planning Burden


By:   |   Read Comments   |   Related Tips: 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


Last Update:






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    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools