Search code examples
sql-serverdatabase-administration

Forecasting the DB size in SQL Server

Today, I got the New requirement from business to forecast the DB size in advance for new project.

Can any one know how can I forecast the DB Size in advance?

we have product which import the DATA from excel to DB the maximum size is any thing like 500 column and 15000 rows.

So, I want create the forecasting formula to find the excepted DB Size. for example if client upload the DATA 500 columns and 15000 Rows the excepted size would be =500*1500 but it's not give exact size.

Solution

  • Column count is not enough, you have to know the data types and the length of the data.

    Using this information, you can calculate the worst case scenario by adding up the maximum storage space required for the columns. For example an NVARCHAR(20) will require 40 bytes when it stores a 20 characters long string.

    Multiply this by the number of rows (this is an educated guess based on previous info).

    This is your approximate growth.

    Here is an example:

    You have these columns:

    • ID INT
    • Name NVARCHAR(64)
    • Description NVARCHAR(256)

    Your row size is 4 bytes + 128 bytes + 512 bytes = 644 bytes per row.

    This is the worst case scenario, in reality the average will be less.

    There is some overhead and you have to consider indexes, log files, backup space, and so on, they do take storage space.

    Since you've mentioned, that this will be a SQL Server Express instance, you have a hard limit for DB size (more or less data and index size combined):

    • Microsoft SQL Server 2008 Express: 4GB
    • Microsoft SQL Server 2008 R2 Express: 10GB
    • Microsoft SQL Server 2012: 10GB
    • Microsoft SQL Server 2014 Express: 10GB

    Resources: