Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Capacity Planning for SQL Server 2000 Database Storage

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (3)   |   Related Tips: More > Database Administration

Problem
I am running very low on storage to support my SQL Server 2000 user defined databases on a number of my production SQL Server instances.  We have made the decision to invest in some sort of NAS\SAN or direct attached storage solution.  Independent of which technology, vendor and RAID sets we select, I need to plan for my database storage for the next 18 to 24 months and beyond.  I have seen your script for capacity planning at a table level (Easing the Capacity Planning Burden), but how can I roll this up at a database level?  I need to determine the storage needs at a database and SQL Server instance.

Solution
To me the best way to determine future storage needs is to analyze the past with an eye out for potential business changes that cannot be reflected from historical calculations.  As such, below is script that calculates the needed storage at a per SQL Server instance and database level.  This script will  review the database backup sizes at a per database level to calculate the storage needs for the future.  Check out this SQL Server database capacity planning script:

Table - SQL Server 2000 Database Capacity Planning

CREATE TABLE [dbo].[CapacityPlanning] (
[CPID] [int] IDENTITY (1, 1) NOT NULL ,
[ServerName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DatabaseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExecutionDateTime] [datetime] NULL ,
[NewDatabaseSize] [decimal](18, 0) NULL ,
[OldDatabaseSize] [decimal](18, 0) NULL ,
[NewCreationDate] [datetime] NULL ,
[OldCreationDate] [datetime] NULL ,
[VarDiff] [decimal](18, 0) NULL ,
[PercentGrowth] [decimal](18, 0) NULL ,
[AvgGrowth] [decimal](18, 0) NULL ,
[DateDiff] [decimal](18, 0) NULL ,
[Yr1DBProjections] [decimal](18, 0) NULL ,
[Yr1LogProjections] [decimal](18, 0) NULL ,
[Yr1DBProjections15Percent] [decimal](18, 0) NULL ,
[Yr1LogProjections15Percent] [decimal](18, 0) NULL ,
[Total1YrProj] [decimal](18, 0) NULL ,
[Total1YrProj15Percent] [decimal](18, 0) NULL ,
[Yr2DBProjections] [decimal](18, 0) NULL ,
[Yr2LogProjections] [decimal](18, 0) NULL ,
[Yr2DBProjections15Percent] [decimal](18, 0) NULL ,
[Yr2LogProjections15Percent] [decimal](18, 0) NULL ,
[Total2YrProj] [decimal](18, 0) NULL ,
[Total2YrProj15Percent] [decimal](18, 0) NULL ,
[Yr3DBProjections] [decimal](18, 0) NULL ,
[Yr3LogProjections] [decimal](18, 0) NULL ,
[Yr3DBProjections15Percent] [decimal](18, 0) NULL ,
[Yr3LogProjections15Percent] [decimal](18, 0) NULL ,
[Total3YrProj] [decimal](18, 0) NULL ,
[Total3YrProj15Percent] [decimal](18, 0) NULL ,
[TotalNumberofDatabases] [int] NULL
) ON [PRIMARY]
GO

Script - SQL Server 2000 Database Capacity Planning

CREATE PROCEDURE spCapacityPlanning AS

/*
----------------------------------------------------------------------------
-- Object Name: spCapacityPlanning
-- Project: Capacity Planning
-- Business Process: Capacity Planning
-- Purpose: Calculate the capacity planning for 1, 2 and 3 years for the database and transaction log
-- Detailed Description: Capture static information and write infromation to the
-- dbo.CapacityPlanning table for the database and transaction log calculations
-- Database: TBD
-- Dependent Objects:
-- - Master.dbo.sysdatabases
- MSDB.dbo.backupset
- TBD.dbo.CapacityPlanning
-- Called By: TBD
-- Upstream Systems: N\A
-- Downstream Systems: N\A

--
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
-- 001 | N\A | 06.15.2007 | Edgewood | Original code
--
*/

SET NOCOUNT ON

-- Step 1 - Preliminary Information
SELECT @@SERVERNAME AS 'Server Name'
SELECT GETDATE() AS 'Execution Timestamp'
PRINT '--------------------------------------------------------'
PRINT '********************************************************'
PRINT ''
SELECT 'Disk Space Availablity'
PRINT ''
PRINT '********************************************************'
PRINT '--------------------------------------------------------'
PRINT ''
PRINT ''
EXEC Master.dbo.xp_fixeddrives


-- Step 2 - Declare the cursor variables
--Prepatory Variables
DECLARE @DatabaseName VARCHAR(50)
DECLARE @ExecutionDateTime DateTime
DECLARE @NewDatabaseSize Decimal
DECLARE @OldDatabaseSize Decimal
DECLARE @NewCreationDate DateTime
DECLARE @OldCreationDate DateTime
DECLARE @VarDiff Decimal
DECLARE @PercentGrowth Decimal
DECLARE @AvgGrowth Decimal
DECLARE @DateDiff Decimal

-- 1 Year Variables
DECLARE @Yr1DBProjections Decimal
DECLARE @Yr1LogProjections Decimal
DECLARE @Yr1DBProjections15Percent Decimal
DECLARE @Yr1LogProjections15Percent Decimal

-- 2 Year Variables
DECLARE @Yr2DBProjections Decimal
DECLARE @Yr2LogProjections Decimal
DECLARE @Yr2DBProjections15Percent Decimal
DECLARE @Yr2LogProjections15Percent Decimal

-- 3 Year Variables
DECLARE @Yr3DBProjections Decimal
DECLARE @Yr3LogProjections Decimal
DECLARE @Yr3DBProjections15Percent Decimal
DECLARE @Yr3LogProjections15Percent Decimal

-- Total Historical Variables
DECLARE @TotalRecentDatabaseSize Decimal
DECLARE @TotalOldDatabaseSize Decimal
DECLARE @TotalDiffDatabaseSize Decimal
DECLARE @TotalPercentageGrowth Decimal
DECLARE @AvgPercentageGrowth Decimal
DECLARE @AvgDateDiff Decimal
DECLARE @TotalNumberofDatabases Decimal
DECLARE @TotalDateDiff Decimal

-- Total Projection Variables
DECLARE @Total1YrDBProj Decimal
DECLARE @Total1YrLogProj Decimal
DECLARE @Total1YrDBProj15Percent Decimal
DECLARE @Total1YrLogProj15Percent Decimal
DECLARE @Total1YrProj Decimal -- Database and Log
DECLARE @Total1YrProj15Percent Decimal -- Database and Log

DECLARE @Total2YrDBProj Decimal
DECLARE @Total2YrLogProj Decimal
DECLARE @Total2YrDBProj15Percent Decimal
DECLARE @Total2YrLogProj15Percent Decimal
DECLARE @Total2YrProj Decimal -- Database and Log
DECLARE @Total2YrProj15Percent Decimal -- Database and Log

DECLARE @Total3YrDBProj Decimal
DECLARE @Total3YrLogProj Decimal
DECLARE @Total3YrDBProj15Percent Decimal
DECLARE @Total3YrLogProj15Percent Decimal
DECLARE @Total3YrProj Decimal -- Database and Log
DECLARE @Total3YrProj15Percent Decimal -- Database and Log

-- Initialize Historical Variables
SELECT @ExecutionDateTime = GETDATE()
SELECT @TotalNumberofDatabases = 0
SELECT @TotalRecentDatabaseSize = 0
SELECT @TotalOldDatabaseSize = 0
SELECT @TotalDiffDatabaseSize = 0
SELECT @TotalPercentageGrowth = 0
SELECT @AvgPercentageGrowth = 0
SELECT @AvgDateDiff = 0
SELECT @TotalDateDiff = 0

SELECT @Total1YrDBProj = 0
SELECT @Total1YrLogProj = 0
SELECT @Total1YrDBProj15Percent = 0
SELECT @Total1YrLogProj15Percent = 0
SELECT @Total1YrProj = 0 -- Database and Log
SELECT @Total1YrProj15Percent = 0-- Database and Log

SELECT @Total2YrDBProj = 0
SELECT @Total2YrLogProj = 0
SELECT @Total2YrDBProj15Percent = 0
SELECT @Total2YrLogProj15Percent = 0
SELECT @Total2YrProj = 0 -- Database and Log
SELECT @Total2YrProj15Percent = 0 -- Database and Log

SELECT @Total3YrDBProj = 0
SELECT @Total3YrLogProj = 0
SELECT @Total3YrDBProj15Percent = 0
SELECT @Total3YrLogProj15Percent = 0
SELECT @Total3YrProj = 0 -- Database and Log
SELECT @Total3YrProj15Percent = 0 -- Database and Log

-- Step 3 - Begin Cursor Processing
DECLARE CapPlanCursor CURSOR FOR

SELECT Name
FROM master.dbo.sysdatabases
ORDER BY Name

OPEN CapPlanCursor

FETCH NEXT FROM CapPlanCursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN
-- Prepatory Calculations

SELECT @NewDatabaseSize = ((backup_size)/1024/1024), @NewCreationDate = (backup_start_date)
FROM MSDB.dbo.backupset
WHERE database_name = @DatabaseName
AND TYPE = 'D'
ORDER BY backup_set_id

SELECT @OldDatabaseSize = ((backup_size)/1024/1024), @OldCreationDate = (backup_start_date)
FROM MSDB.dbo.backupset
WHERE database_name = @DatabaseName
AND TYPE = 'D'
ORDER BY backup_set_id DESC

SELECT @VarDiff = (@NewDatabaseSize - @OldDatabaseSize)

SELECT @PercentGrowth = (((@NewDatabaseSize/@OldDatabaseSize)-1)* 100)

SELECT @DateDiff = DATEDIFF(dd, @OldCreationDate, @NewCreationDate)

SELECT @AvgGrowth = (@VarDiff/@DateDiff)

-- Year 1 Figures
SELECT @Yr1DBProjections = ((@AvgGrowth * 365) + @NewDatabaseSize)
SELECT @Yr1DBProjections15Percent = ((@Yr1DBProjections * .15) + @Yr1DBProjections)
SELECT @Yr1LogProjections = (@Yr1DBProjections/4)
SELECT @Yr1LogProjections15Percent = ((@Yr1LogProjections * .15) + @Yr1LogProjections)

-- Year 2 Figures
SELECT @Yr2DBProjections = ((@AvgGrowth * 730) + @NewDatabaseSize)
SELECT @Yr2DBProjections15Percent = ((@Yr2DBProjections * .15) + @Yr2DBProjections)
SELECT @Yr2LogProjections = (@Yr2DBProjections/4)
SELECT @Yr2LogProjections15Percent = ((@Yr2LogProjections * .15) + @Yr2LogProjections)

-- Year 3 Figures
SELECT @Yr3DBProjections = ((@AvgGrowth * 1095) + @NewDatabaseSize)
SELECT @Yr3DBProjections15Percent = ((@Yr3DBProjections * .15) + @Yr3DBProjections)
SELECT @Yr3LogProjections = (@Yr3DBProjections/4)
SELECT @Yr3LogProjections15Percent = ((@Yr3LogProjections * .15) + @Yr3LogProjections)

-- Calculation Totals
SELECT @TotalRecentDatabaseSize = @TotalRecentDatabaseSize + @NewDatabaseSize
SELECT @TotalOldDatabaseSize = @TotalOldDatabaseSize + @OldDatabaseSize
SELECT @TotalDiffDatabaseSize = @TotalDiffDatabaseSize + @VarDiff
SELECT @TotalNumberofDatabases = @TotalNumberofDatabases + 1
SELECT @TotalPercentageGrowth = @TotalPercentageGrowth + @AvgGrowth
SELECT @TotalDateDiff = @TotalDateDiff + @DateDiff

-- Year 1 Projection Totals
SELECT @Total1YrDBProj = @Yr1DBProjections + @Total1YrDBProj
SELECT @Total1YrLogProj = @Yr1LogProjections + @Total1YrLogProj
SELECT @Total1YrDBProj15Percent = @Yr1DBProjections15Percent + @Total1YrDBProj15Percent
SELECT @Total1YrLogProj15Percent = @Yr1LogProjections15Percent + @Total1YrLogProj15Percent

-- Year 2 Projection Totals
SELECT @Total2YrDBProj = @Yr2DBProjections + @Total2YrDBProj
SELECT @Total2YrLogProj = @Yr2LogProjections + @Total2YrLogProj
SELECT @Total2YrDBProj15Percent = @Yr2DBProjections15Percent + @Total2YrDBProj15Percent
SELECT @Total2YrLogProj15Percent = @Yr2LogProjections15Percent + @Total2YrLogProj15Percent

-- Year 3 Projection Totals
SELECT @Total3YrDBProj = @Yr3DBProjections + @Total3YrDBProj
SELECT @Total3YrLogProj = @Yr3LogProjections + @Total3YrLogProj
SELECT @Total3YrDBProj15Percent = @Yr3DBProjections15Percent + @Total3YrDBProj15Percent
SELECT @Total3YrLogProj15Percent = @Yr3LogProjections15Percent + @Total3YrLogProj15Percent

-- Insert values into the dbo.CapacityPlanning table
INSERT INTO dbo.CapacityPlanning
(
ServerName
,DatabaseName
,ExecutionDateTime
,NewDatabaseSize
,OldDatabaseSize
,NewCreationDate
,OldCreationDate
,VarDiff
,PercentGrowth
,AvgGrowth
,DateDiff
,Yr1DBProjections
,Yr1LogProjections
,Yr1DBProjections15Percent
,Yr1LogProjections15Percent
,Yr2DBProjections
,Yr2LogProjections
,Yr2DBProjections15Percent
,Yr2LogProjections15Percent
,Yr3DBProjections
,Yr3LogProjections
,Yr3DBProjections15Percent
,Yr3LogProjections15Percent
)
VALUES
(
@@ServerName
,@DatabaseName
,@ExecutionDateTime
,@NewDatabaseSize
,@OldDatabaseSize
,@NewCreationDate
,@OldCreationDate
,@VarDiff
,@PercentGrowth
,@AvgGrowth
,@DateDiff
,@Yr1DBProjections
,@Yr1LogProjections
,@Yr1DBProjections15Percent
,@Yr1LogProjections15Percent
,@Yr2DBProjections
,@Yr2LogProjections
,@Yr2DBProjections15Percent
,@Yr2LogProjections15Percent
,@Yr3DBProjections
,@Yr3LogProjections
,@Yr3DBProjections15Percent
,@Yr3LogProjections15Percent
)

FETCH NEXT FROM CapPlanCursor INTO @DatabaseName

END

-- Step 4 - Calculate Aggregates
-- Historical Totals
SELECT @AvgPercentageGrowth = (@TotalPercentageGrowth/@TotalNumberofDatabases)
SELECT @AvgDateDiff = (@TotalDateDiff/@TotalNumberofDatabases)

-- Year 1 Totals
SELECT @Total1YrProj = @Total1YrDBProj + @Total1YrLogProj -- Database and Log
SELECT @Total1YrProj15Percent = @Total1YrDBProj15Percent + @Total1YrLogProj15Percent -- Database and Log

-- Year 2 Totals
SELECT @Total2YrProj = @Total2YrDBProj + @Total2YrLogProj -- Database and Log
SELECT @Total2YrProj15Percent = @Total2YrDBProj15Percent + @Total2YrLogProj15Percent -- Database and Log

-- Year 3 Totals
SELECT @Total3YrProj = @Total3YrDBProj + @Total3YrLogProj -- Database and Log
SELECT @Total3YrProj15Percent = @Total3YrDBProj15Percent + @Total3YrLogProj15Percent -- Database and Log

-- Step 5 - Insert Into Capacity Planning Table
INSERT INTO CapacityPlanning
(ServerName
,DatabaseName
,ExecutionDateTime
,NewDatabaseSize
,OldDatabaseSize
,NewCreationDate
,OldCreationDate
,VarDiff
,PercentGrowth
,AvgGrowth
,DateDiff
,Yr1DBProjections
,Yr1LogProjections
,Yr1DBProjections15Percent
,Yr1LogProjections15Percent
,Total1YrProj
,Total1YrProj15Percent
,Yr2DBProjections
,Yr2LogProjections
,Yr2DBProjections15Percent
,Yr2LogProjections15Percent
,Total2YrProj
,Total2YrProj15Percent
,Yr3DBProjections
,Yr3LogProjections
,Yr3DBProjections15Percent
,Yr3LogProjections15Percent
,Total3YrProj
,Total3YrProj15Percent
,TotalNumberofDatabases
)
VALUES
(
@@ServerName
,'Total Calculations'
,@ExecutionDateTime
,@TotalRecentDatabaseSize
,@TotalOldDatabaseSize
,NULL
,NULL
,@TotalDiffDatabaseSize
,NULL -- @AvgPercentageGrowth
,@TotalPercentageGrowth
,@AvgDateDiff
,@Total1YrDBProj
,@Total1YrLogProj
,@Total1YrDBProj15Percent
,@Total1YrLogProj15Percent
,@Total1YrProj
,@Total1YrProj15Percent
,@Total2YrDBProj
,@Total2YrLogProj
,@Total2YrDBProj15Percent
,@Total2YrLogProj15Percent
,@Total2YrProj
,@Total2YrProj15Percent
,@Total3YrDBProj
,@Total3YrLogProj
,@Total3YrDBProj15Percent
,@Total3YrLogProj15Percent
,@Total3YrProj
,@Total3YrProj15Percent
,@TotalNumberofDatabases
)

-- Step 6 - Generate Report
SELECT *
FROM CapacityPlanning
WHERE ExecutionDateTime = @ExecutionDateTime

CLOSE CapPlanCursor

DEALLOCATE CapPlanCursor

SET NOCOUNT OFF

GO

Special Notes

  • To the best of my knowledge, this script will work for SQL Server databases that are using third party backup products (SQL Server 2005 Backup Product Options). This is because the values in backup system tables in the MSDB database are capturing values from the SQL Server Virtual Device Interface (VDI) as opposed to the compressed values from the third party product.  This means that the backups can give you a historical view to use as a reasonable planning tool.
  • All businesses change and therefore those applications change as well.  Some applications are retired while others are brought into the production environment.  As such, this script will not be able to help estimate the future growth without having a representative amount of backups for calculations.
    • On a related, if you know you are bringing on a new customer that is going to double the revenue for the organization, odds are that you are going to have to plan for this storage as well independent of the script listed in this tip.  You may be able to anecdotally plan based on the script, but the script is making a prediction on the storage needs based on the historical trends.

Next Steps

  • Keep an eye on the available storage across your SQL Server 2000 instances.  When you are faced with the daunting task of determining how much storage you will need to support your databases at a SQL Server instance level, consider this script. 
  • As a proactive DBA, it would be a good idea to monitor the storage on your SQL Server instances to determine how long the current storage will support the current databases at the current growth rate.
  • Once you have calculated the amount of storage that is needed to support your databases at a SQL Server instance level, make sure you will have sufficient storage based on the RAID set, hot spares, etc. that are needed.  Work with your vendor to figure out the needed storage based on the hardware capabilities and\or functionality.


Last Update: 6/18/2007


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, June 22, 2012 - 2:27:01 AM - Dinesh Vishe Read The Tip

Great ONe.............Very usefulllllll


Tuesday, August 05, 2008 - 2:36:23 AM - mikedobing Read The Tip

Hi,

 

It's the denominator that's the problem. Try something along the lines of:

case when @olddatabasesize = 0 then 0 else  (((@newdatabasesize/@olddatabasesize) -1)*100) end

you only should get that error when the denom is 0, so by changing the whole result to 0 when this occurs should give you a valid result.

 

Cheers

 


Tuesday, July 29, 2008 - 7:57:22 AM - GregReno Read The Tip

Nice article.

One question if I may, I am new to this and tried a few things but it did not work.

How can I handle this error

Msg 8134, Level 16, State 1, Procedure spCapacityPlanning, Line 171

Divide by zero error encountered.

Here is the command. 

SELECT @PercentGrowth = ( ( ( @NewDatabaseSize / @OldDatabaseSize ) - 1 ) * 100 )

 I tried this and a few other variations but it leaves every result as null.

SELECT CASE WHEN @OldDatabaseSize <> 0 THEN ( ( ( @NewDatabaseSize / @OldDatabaseSize ) - 1 ) * 100 )
ELSE 0
END AS '@PercentGrowth'

and

SELECT CASE WHEN @NewDatabaseSize = 0 or @OldDatabaseSize = 0 THEN 0
ELSE ( ( ( @NewDatabaseSize / @OldDatabaseSize ) - 1 ) * 100 )
END AS '@PercentGrowth'

Any help appreciated

 

Thank you,

Greg




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.