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".

Estimating hardware requirements for a SQL Server 2005 installation

Is there any good online documentation describing rules of thumb for estimating and planning a SQL Server 2005 deployment in support of a Windows rich client/WCF services architecture?

TIA,

Ian
Well you do not want to hear that answer, but it depends.It′s like asking, How much gas will I need when I travel from NYC to Seattle. Well that depends on how much data / luggage will be transported, which transport device you use, how much data will be retrieved, how often you will query the database, how many users will access the database, etc. There are many parameters to consider.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

ErrorFile option

The documentation point out that "distribution agent utility" can accept the
option "-ErrorFile". But the distributor always refuse it. Why?
Thanks
you only get this file generated if there is an error. You can find these
files by default in %windir%\system32
Microsoft has not provided a mechanism to clean these files up, so you will
have to delete them manually.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Fausto" <Fausto@.discussions.microsoft.com> wrote in message
news:9AF87ED3-2F63-467E-9629-DD931DF25A88@.microsoft.com...
> The documentation point out that "distribution agent utility" can accept
the
> option "-ErrorFile". But the distributor always refuse it. Why?
> Thanks
>