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

estimate of transaction log size for reindexing

Hello,
Is there anyway to estimate the transaction log files needed to reindex all
tables in a database?
I mean any formula that can be used?
regards,
meLook at how big your indexes are now. this is different depending on the
version of sql server you are on. tlog will vary depending on this size and
the type of defrag operation done.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:FF5149C0-A8B3-4B34-BFE3-D18A7E2ED15E@.microsoft.com...
> Hello,
> Is there anyway to estimate the transaction log files needed to reindex
> all
> tables in a database?
> I mean any formula that can be used?
> regards,
> me|||Hi
You have to identify what tables are defragmented and run rebuild index only
on them. Please tell us what SQL Server you are using?
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:FF5149C0-A8B3-4B34-BFE3-D18A7E2ED15E@.microsoft.com...
> Hello,
> Is there anyway to estimate the transaction log files needed to reindex
> all
> tables in a database?
> I mean any formula that can be used?
> regards,
> mesql

Errors using DATEDIFF and DATEADD formulas

I am trying to use the DATEDIFF function in a formula, but it isn't
working. Here is the formula that I am entering into the "Define
Formula" dialog box:
DATEDIFF("day", TimeLogged, TimeClosed)
When I click ok, it gives me the message, "The arguments to the
following function are not valid: DATEDIFF". With that in mind, I
looked in the Help file for the report builder and under the "DATEDIFF
statement" documentation, it had the following example:
DATEDIFF("month", #1/1/2009#, #3/31/2009#)
This one yielded the same results. I also tried the DATEADD formula
and got the same type of error message. Other formulas, such as YEAR,
WEEK, etc. work fine.
Does anyone have any suggestions? I get the same results using both
the Report Builder and Visual Studio 2005.Hi,
the syntax is like this.
DATEDIFF("d", TimeLogged, TimeClosed)
This will work.
Amarnath.
"photogulliver" wrote:
> I am trying to use the DATEDIFF function in a formula, but it isn't
> working. Here is the formula that I am entering into the "Define
> Formula" dialog box:
> DATEDIFF("day", TimeLogged, TimeClosed)
> When I click ok, it gives me the message, "The arguments to the
> following function are not valid: DATEDIFF". With that in mind, I
> looked in the Help file for the report builder and under the "DATEDIFF
> statement" documentation, it had the following example:
> DATEDIFF("month", #1/1/2009#, #3/31/2009#)
> This one yielded the same results. I also tried the DATEADD formula
> and got the same type of error message. Other formulas, such as YEAR,
> WEEK, etc. work fine.
> Does anyone have any suggestions? I get the same results using both
> the Report Builder and Visual Studio 2005.
>