Formula to calculate future database growth 1

[vadimg]

Hi Gurus,
what formulas do you use to estimate future db size growth. (for example: my db were 30GB in 2001, 300GB in 2002, and 1TB now, what should i plan for in 1,2,3 years?)

Also, is there an industry-wide accepted formula?

Thanks.

 

[TheBugSlayer]

Hmmm, what you have to do is take metrics of your database activity. For instance how many rows are added to your tables in average over a certain period of time. From the table structure you can tell the size of a record. That would give you a reliable rough estimate; there is other info that takes space in a table too, headers, indexes, etc.

The stored procedure sp_spaceused tells you the space occupied by each table (look for a combination with sp_MSForEachTable). So you could assess the growth over a period of time and forecast from there.

 

[SQLSister]

I don't know if you can do this with a set formula because so much is dependent on why the growth occured or what is planned for the future. For instance the size of my database recently doubled becasue we were adding a new functionality. But that isn't indicative of future growth because once the new data was in there, the relative number of records will stay roughly the same as the replaced every hour with new data.

On the other hand, I know we have marketing plans which will surely increase the number of users and consequently their users record so that is growth I can plan for. I also know that another planned new functionaity will be occurring in the next two years which will probably increase the size of my database 100 fold, I wouldn't be able to see that just looking at a formula based on past growth patterns.

 

[vadimg]

SQLSister, i agree with you completely.
The powers above me have trouble understanding that storage growth rates can not be correlated to the increasing number of records in a database. They are rather driven by evolving business needs and new technologies.

i just wanted to find out if there some industry-accepted formula, but i guess the answer is no (and rightly so).

Thanks!

 

[TheBugSlayer]

You STILL can estimate the growth partially. There are things you cannot predict - not that it is impossible, you may have to buy expert advice from M$- but you CAN estimate table size and growth quite reliably. That is the known factor in your formula. This kind of approximate information is what you use when you plan on buying storage for instance.

 

[MDXer]

If you search Books Online for "Estimating the Size of a Table" it provides the formulas needed to do a datasize projection. If you know your potential growth is xmillion records then you can safely project the storage requirements for that growth.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[JayKusch]

Here is an SP I use to monitor the growth of DBs out in the field. If you have a test server, with the DB set up on it, you could run this proc initially to get the zero value. Then run a test set of data, say a million records or whatever number you feel is adaquate and then re-run the SP to see the differences. You can then rerun your test set of data and the SP to get a feel for the growth of the DB.


CREATE PROC usp_Monitor_DB_Growth
( @dbnameParam sysname = NULL )

AS

BEGIN

DECLARE @dbname sysname

/* Work with current database if a database name is not specified */

SET @dbname = COALESCE(@dbnameParam, DB_NAME())

TRUNCATE TABLE Monitor_DB_Growth

INSERT INTO Monitor_DB_Growth

SELECT @dbname AS [Database Name],
'DB' AS [DB_or_TLog],
CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format
CONVERT(char, backup_start_date, 108) AS [Time],
CONVERT(numeric(15,2),file_size/1048576) AS [File Size (MB)],
Growth AS [Growth Percentage (%)],
GetDate() AS [Date_Stats_Pulled]
FROM
(
SELECT b.backup_start_date,
a.backup_set_id,
a.file_size,
a.logical_name,
a.[filegroup_name],
a.physical_name,
(
SELECT CONVERT(numeric(15,2),((a.file_size * 100.00)/bf.file_size)-100)
FROM MSDB.dbo.backupfile bf
WHERE bf.backup_set_id =
(
SELECT MAX(bf2.backup_set_id)
FROM MSDB.dbo.backupfile AS bf2
JOIN MSDB.dbo.backupset AS bs
ON bf2.backup_set_id = bs.backup_set_id
WHERE bf2.backup_set_id < a.backup_set_id AND
bf2.file_type ='D' AND
bs.database_name = @dbname AND
bf2.logical_name = a.logical_name AND
bf2.logical_name = bf.logical_name AND
bs.type = 'D'
) AND
bf.file_type = 'D'
) AS Growth
FROM MSDB.dbo.backupfile AS a
JOIN MSDB.dbo.backupset AS b
ON a.backup_set_id = b.backup_set_id
WHERE b.database_name = @dbname AND
a.file_type = 'D' AND
b.type = 'D'

) AS Derived
WHERE (Growth <> 0.0) OR
(Growth IS NULL)
ORDER BY logical_name, [Date]

END
GO




Thanks

J. Kusch