Estimating the database size

How do I estimate the database size in Sybase ASE or ASA? Is there any formula for this calculation, including the overheads and system usage (such as index)?

When creating a new database, the size allocation decisions you make are important, because it is difficult to reclaim storage space after it has been assigned. You can always add space; however, you cannot de-allocate space that has been assigned to a database, unless you drop the database first which is obviously not very ideal.

The most critical space consumables in a database are tables and indexes. Sybase provides a routine called "sp_estspace" to allow you to plan for the future growth of your tables and indexes. You provide a table name, and the number of rows you expect to have in the table, and sp_estspace estimates the size for the table and for any indexes that exist. It does not look at the actual size of the data in the tables.

To use sp_estspace:

• Create the table, if it does not exist.
• Create any indexes on the table.
• Execute the procedure, estimating the number of rows that the table will hold.

The following example from Sybase estimates the size of the titles table with 500,000 rows, a clustered index, and two nonclustered indexes:

sp_estspace titles, 500000

name                  type         idx_level Pages    Kbytes      
--------------------- ------------ --------- -------- --------
titles                data                 0    50002   100004
title_id_cix          clustered            0      302      604
title_id_cix          clustered            1        3        6
title_id_cix          clustered            2        1        2
title_ix              nonclustered         0    13890    27780
title_ix              nonclustered         1      410      819
title_ix              nonclustered         2       13       26
title_ix              nonclustered         3        1        2
type_price_ix         nonclustered         0     6099    12197
type_price_ix         nonclustered         1       88      176
type_price_ix         nonclustered         2        2        5
type_price_ix         nonclustered         3        1        2

Total_Mbytes     
-----------------
  138.30

name                  type         total_pages  time_mins   
--------------------- ------------ ------------ ------------
title_id_cix          clustered           50308          250
title_ix              nonclustered        14314           91
type_price_ix         nonclustered         6190           55
 

Whenever you estimate a database size, it is a good practice to make sure that your estimates of growth are good enough for a year or so of your database growth. Once you have estimated the total size for your tables and indexes, a rule of thumb is to increase it by 25% to cover for your other overheads. Remember that all these are estimates and in most cases, they turn up to be underestimates! To estimate the size of your transaction log, you can go for around 20% of your estimated data size of your database. For example, if your data part of your database is around 1000 MB, make the size of your transaction log 200 MB or so.