Home > Database, SQL Server > How do I estimate database growth?

How do I estimate database growth?

Unfortunately, SQL Server does not come with any built-in tools to estimate database size.
If you don’t have a third-party estimating tool, you have to do it the hard way, which is by calculating the space taken up by each table, and then adding the total space needed for each table to get a grand total for the database.

Here are some guidelines for estimating the space needed for a database.

  1. For each table, find out how many bytes each row will use up on average. It is easy to calculate the size of fixed length columns, but calculating the space used by variable length fields is much more difficult. About the only way to do this is to get some of the actual data that will be stored in each variable length column, and then based on the data you have, estimate the average byte length of each variable length column. Once you know the typical sizes of each column, you can then calculate the size of each typical row in your table.
  2. The above step is a good start, but it usually significantly underestimates the amount of space you need. Besides the actual data, you must also estimate the type and number of indexes you will use for each table. Indexes can use a huge amount of space, and you must estimate how much space you think they will take. This is a function of the type of index (clustered or non-clustered, the number of indexes, and the width of the indexes).
  3. Besides estimating the size of the indexes, you also must take into consideration the Fillfactor and Pad Index used when the indexes are created. Both of these affect how much empty space is left in an index, and this empty space must be included in your estimate.
  4. And one more factor affecting how much space it takes to store data in a table is how many rows can be fitted onto one SQL Server 8K data page. Depending on the size of each row, it is likely that not all of the space in each data page is fully used. This must also be accounted for when estimating page size.
  5. While tables, and their associated indexes take up most of the physical space in most databases, keep in mind that every object in SQL Server takes up space, and must be accounted for.

As you can see, without a tool to help out, manually estimating database size is not a fun task, and it is, at best, only a rough estimate.

Another option you might consider, assuming that you already have an existing database with data, is to extrapolate the current size of your database on a table by table basis. For example, if you know that a particular table has 100,000 rows, and it is 1MB in size, then assuming that neither indexing or the fillfactor changes, than when the table gets to 200,000 rows, that it should be about 2MB in size. If you do this for every table, then you can get a fairly good idea on how much disk space you will need in the future. To find out how much space a particular table uses, use this command:

sp_spaceused ' '

Categories: Database, SQL Server
  1. February 11, 2007 at 9:27 pm

    Hi, I am wondering why a database that was 2Gb in size would come down to just 300Mb when I copy all the data from the origional database into a blank new one.
    I ran a database shrink on the origional database but that only reduced it from 2.1 Gb to 2Gb.
    I was shocked to see the size difference when I copied the data.
    As far as I can tell the copied database still contains all the records of the origional.

    I remember that MSAccess had a Compact function that literally copied all the data from one databse into a new empty one – is there the equivalent for SQL?

    Any thoughts?


  2. May 29, 2007 at 6:04 pm

    you cannot shrink database smaller than the original size(size when created the db)
    In ur case it seems the database was created with 2GB size.

  3. geile
    September 6, 2007 at 5:17 am

    Das is kein daustch , du muss english learnen kollege

  4. Elia
    October 10, 2008 at 8:45 am

    I have a task to design and estimate the total size (space requirement) for a database. How do I accompolish this. Can someone help please.

  1. June 2, 2007 at 9:25 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: