Is there a data sizer tool available for SQL Server 2005?

Question

I have to estimate the data size of database when populated with forecast values for each table. It should at least include size to be used for data and indexes.

There used to be tool like this in SQL Server 2000 that produced an estimated size when supplied with the amount of records per table (I think it shipped with the SQL 2000 resource kit). I can neither find this tool for 2000 or 2005 :(

I know about sp_spacedused but that is not going to work for me as I have to pre-populate the database with values and I have potentially many scenarios to calculate.

Answer 1

Hi,

Have you considered using a tool to generate dummy data?

You could create tables with enough data to provide a good estimate (i.e. create some representative data and then factor it up) of your production databases size.

You may find Tools such as Red Gates SQL Data Generator to be of use.

http://www.red-gate.com/products/SQL_Data_Generator/index.htm

Cheers, John

Answer 2

Last time I did this it was excel, pencil, back of envelope and educated guess work. The answer wasn't too inaccurate. I guess you're talking serious data volumes as disk is so cheap/available these days that all but the biggest DBs will run on small(ish) servers.

Can you give more details of expected row counts and the kind of data you'll be storing. BTW don't go with Blobs/images/video in the DB - it's an ugly thing once it scales.

Answer 3

In your shoes, I'd probably just create the database and try to fill it with a representative sample of data and then check what the table sizes look like.

This SQL script does it over all tables - you don't have to call sp_spaceused on each and every single table:

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN   
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND  
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    object_name(i.object_id)

Cheers, Marc

Answer 4

Please check this link http://www.dell.com/content/topics/global.aspx/tools/advisors/sql_advisor?c=us&cs=555&l=en&s=biz