Estimating DB Size and Growth

  • Is is possible to calculate the size of a SQL table using the following formula?

       Table Size = Record Length (sum of all column lengths) * Number of Records

    Next step is to calculate total database size, any suggestions how I can move from individual table size to total database size?

     

     

  • In books online it discusses how to estimate the size of a table.

    The "Formula" you show is way too simple. There is a space cost for each different datatype, and whether it allows NULL values or not. #Rows per page, and indexes.

    the page lists how to calculate a row size, then # of rows that can fit on a page. And it also includes how to figure indexes. Then after you doo all that you want to triple the # for your working drive space needs.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_92k3.asp

  • There's already a system sp to do what you're looking for.  We had to do this same thing just last week.  We modified it a bit to report the size of specific tables, and multiple databases, but pretty easy.

    Use sp_spaceused.  See BOL for more description of what sp_spaceused does.

  • Thank you, this was extremely helpful.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply