Calculating the Disk Size for the SQL Server

In order to ensure that the SQL Server has enough storage for the Reporting databases, use the calculation below to determine the size needed for the SQL Server that will host the Staging and Datamart database.

Below are also some tips on how to efficiently utilize the storage.

Calculate the Disk Space required

MinRequiredDiskSpace = MaxPartitionSize x (KeepDataMonths + 1 + (PartitionFactor x 2) ) + LOG size + PRIMARY size

Where:

  • MaxPartitionSize: is the size of the largest already existing partition;

  • KeepDataMonths: configured number of months of retention (from the setup.bat);

  • + 1: for the partition just older than the retention for the short time before it gets deleted at the start of a new month;

  • + (PartitionFactor x 2): for the start of a new month where ETL created a partition for the current new month and a partition for the next month both with an initial size of "MaxPartitionSize x PartitionFactor";

  • LOG size and PRIMARY size totals are available from File Groups properties, view-able via the SSMS or using the arfax utility.

Notes

  • PartitionFactor is different for each version of SQL Reporting:

0 in SQL Reporting version 11.0 and 11.3 (10MB is close enough to 0)

0.5 in SQL Reporting version 11.4.

0.1 by default in SQL Reporting version 11.5 or later, and is configured in 'settings' table.

*In SQL Reporting version 11.5 or later, check the PartitionFactor in the settings table to confirm: select * from [BigDataInsight<product>].settings

  • KeepDataMonths is also verifiable by querying the settings table: select * from [BigDataInsight<product>].settings

  • This formula applies to SQL Reporting versions 11.0, 11.3 and later.

Example:

  • The largest partition is 200GB, retention is 6 months, partition factor is 0.5.

 Therefore MinRequiredDiskSpace = 200 x 8 = 1.6TB

Provide feedback on this article