Estimating Size of a Table

There's seem to be a problem with the formula given from the documentation of SQL Server2K:

The formula for computing :

RowsPerPage = 8096 */(RowSize+2);
FreeRowsPerPage = 8096 * (100-FillFactor)/100)/(RowSize+2)
NumberOfPages = NumRows/(RowsPerPage - FreeRowsPerPage)
TableSize = 8192 * NumberOfPages

Question...what if the FillFactor is zero,
the NumberOfPages will have an error (divide by zero)....

Even if this is greater than zero (eg 1, 2), the TableSize computed is too big if compared with the output from SP_SPACEUSED...

Can anyone help me on this?
Thank.The FillFactor is a bit of a fudge factor. It is the minimum percentage of the page that will be filled. If you supply a fillfactor of 1, and the average row takes up 75% of the page, then you get about the same actual result as if you had made the fillfactor 74.

One other thing to note is that fillfactor is only used at the creation of an index. As time goes by, and rows are updated, inserted, and deleted, the actual page usage can vary widely.

Hope this helps.
~Matt|||Thanks for the reply Matt.

I have a very large table (abt 7M rows). The time I created it, I didnt specify the FillFactor, so it defaults to 0 as stated in the doc. So I thought I should use the same FillFactor in the formula, but then it gave me too big a tablesize. From my understanding on your reply that actual page usage varies after a number of DML statements, does it mean that the formula given may not apply anymore?

I'm creating a program to get the tablesize (from this value, also growth rate) of all my tables (from production db) and put them into a table.

Is there a another accurate way for me to get the tablesize other than the SP_SPACEUSED?

Btw, how accurate is the SP_SPACEUSED?|||That is correct, if the table has undergone significant data modifications (insert, update, delete), then the formula is not going to be a very good guide. sp_spaceused can also drift over time, as the data is modified. I have seen import tables that have large amounts of I/O (truncate and bcp) become negative in size, but DBCC UPDATEUSAGE will clear that up admirably.

sp_spaceused is fairly accurate at times. If you can manage to run DBCC UPDATEUSAGE(0) on the database before you run sp_spaceused, you will get almost exact results. At least, as exact as anything in MS SQL ;-).|||RE:
Q1 ...If you can manage to run DBCC UPDATEUSAGE(0) on the database before you run sp_spaceused, you will get almost exact results. At least, as exact as anything in MS SQL ;-).

A1 You may wish to consider running sp_SpaceUsed with the update option (for each object if that best meets the requirement). If sp_SpaceUsed is run frequently that will address the issue. For example:

Use Pubs
Go
-- Update usage for entire Pubs DB:
Exec sp_SpaceUsed
@.updateusage = 'True'
Go

-- Update usage for Authors table only:
Exec sp_SpaceUsed
@.objname = 'Authors',
@.updateusage = 'True'

RE:
Q2 ...Is there a another accurate way for me to get the tablesize other than the SP_SPACEUSED?

Q3 Btw, how accurate is the SP_SPACEUSED?

A3 The sp_SpaceUsed proc queries file page use data, file data, etc., there is not likely a more accurate method. (A2-->) However, nothing prevents one from using it as a starting point in an effort to make a more accurate "sp_MoreAccurateSpaceUsed".