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 55sp_estspace also allows you to specify a fillfactor, the average size of variable-length fields and text fields, and the I/O speed. For more information, see the Sybase reference Reference Manual for sp_estspace.
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.