solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

Idera - SQL diagnostic manager

SQL diagnostic manager is a powerful performance monitoring and diagnostics solution that proactively alerts administrators to health, performance or availability problems within their SQL Server environment via a central console or mobile device. SQL diagnostic manager minimizes costly server downtime by providing agent-less, real-time monitoring and customizable alerting for fast diagnosis and remediation of SQL Server performance and availability problems. SQL diagnostic manager also provides a 'community' environment where, using Idera's IntelliFeed(TM) technology DBAs form a community to collaborate to quickly resolve problems, perform maintenance and capture best practices.

Learn more!

Easing the SQL Server Database Capacity Planning Burden

By: Edgewood Solutions | Read Comments | Print

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

Related Tips: More

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
Read more tips on System Information on MSSQLTIPS

Related Tips: More | Become a paid author

Last Update: 11/30/2006

Share:

Connect with MSSQLTips.com

Comments and Feedback:

Sponsor Information

Try the free performance monitoring tool from Idera!

Quickly and accurately deploy database changes with Red Gate's SQL Compare - the industry standard comparison and deployment tool.

What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check starting at $995.

Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy.

Join the over million SQL Server Professionals who get their issues resolved daily.

Valuable SQL Server web casts on Performance Tuning, Development, Administration, SSIS and more...