I've been tasked with estimating the approximate amount of disk space
required for a new SQL Server database. I have the initial and expected
record counts, but I'm unsure how to translate that into disk space
requirements. How can I best determine this? Is there a formula that new
users like me can reference?

I have a master table and six ancillary tables. For each table, there are
fixed-width character, numeric, and currency fields. I can add up the
fixed-width character fields to produce the record length for each table;
however, I'm unsure how numeric, currency, and ID fields contribute to the
record lengths.

Do page lengths come into play, also? How can I determine how much space
indexes will take up? Are there any other considerations I'm missing?
Thanks!

BOL documents the exact number of bytes consumed by each datatype that you
define within your tables. Add up the bytes per row in the table definition
and then multiply by the number of rows. Repeat that for each index on a
table as well. That will give you the rough estimate which is going to be
good enough. (Yes, you can add in nullability factors and all of the rest
of that stuff, but if you have to calculate a database size that closely,
you really need to buy additional hard drives.)

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.

[quoted text, click to view]

See if these help:


http://www.dell.com/us/en/esg/topics/products_size_pedge_sizing.htm Dell
Sizing tool
http://h71019.www7.hp.com/ActiveAnswers/Render/1,1027,519-6-100-225-1,00.htm
HP Sizing tool

If you look in BooksOnLine for the datatypes you will see how much space
each type uses.

--
Andrew J. Kelly SQL MVP


[quoted text, click to view]

In addition to the other responses, you have to consider the FILLFACTOR for
the Cluster Index for how many rows per page you are going to get (SQL
Server pages are 8 KB in size).

The indexes work similarly, add up the field lengths for the non-clustered
index attributes and add the cluster index width to this (it's the pointer
to the data and must be stored with the index key). These indexes also have
8 KB pages that also must be adjusted for FILLFACTOR.

You will also need to consider free space and growth. If you rebuild your
indexes to defragment, then you will need to have sufficient free space in
the database to defrag your largest table.

You must also consider the associated amount of transaction log space will
be consumed. This will depend on the size of your largest active
transactions, the recovery model chosen, and the frequency of any
transaction log backups.

As a general rule of thumb, leave anywhere from 15% to 50% free space in the
data files, and size your transaction logs to about 2/3 of the space
consumed by the data files. Adjust your backup schedule to maintain the
size of your transaction log at this number or less.

Sincerely,


Anthony Thomas


--

[quoted text, click to view]