disk drive sizing for Advanced Reporting BigDataInsightUC SQL - space needed for X months retention

[GeraldC1]

I want to avoid SQL server disk getting full/run out of disk space/data gaps in reports.

How much disk space do I need for current retention?

How much disk space do I need in order to increase the current retention months of data to keep?

Is there a formula to calculate the disk space required?  

Can I make it use additional disk drives?

[GeraldC1 · Accepted solution]

Answers:

1. Is there a formula? Yes.

RequiredDiskSpace > MaxPartitionSize x (KeepDataMonths+1+(PartitionFactor x 2)) + LOG size + Primary total size

where:

• MaxPartitionSize: size of largest already existing partition; 
• KeepDataMonths: configured number of months of retention;
• +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". 

Notes:

• PartitionFactor is different for each version of Reporting:
  0 in 11.0 and 11.3 versions of Reporting (10MB is close enough to 0)
  0.5 by default in 11.4 or as configured in 'dbo.settings' table.
  0.1 by default in 11.5 or as configured in 'dbo.settings' table.
  *In 11.4 and 11.5 check the PartitionFactor in the settings table to confirm:
  use BigDataInsightUC; select * from settings
  
• KeepDataMonths is also verifiable by querying the settings table:
  use BigDataInsightUC; select * from settings
• This formula applies to Advanced Reporting versions 11.0, 11.3, 11.4 and 11.5.

Example:

• The largest partition is 200GB, retention is 6 months, partition factor is 0.5.
   Therefore RequiredDiskSpace > 200 x 8 = 1.6TB

2. Can partitions be put onto other drives? Yes - by manually creating future file groups onto the additional drive(s). If the file group name and file name match what ETL expects then the ETL will use those instead of attempting to create them, and it will auto-purge them once they are older than the configured retention. See this Help article for the required naming conventions.

Reporting and Analytics > Reporting for UC/Contact Center > Setup Procedures > FAQs

HTH

----------------------------------------

Need to change the retention months? See this post for details:

https://community.ir.com/t5/Unified-Communications/In-Advanced-Reporting-can-I-change-how-many-month...

[Rich]

From at least 11.6 onwards the "next month" partition is created a few days later than previously, and thus the corresponding "expired" month should have been deleted.
So, lets say you have KeepDataMonths=12 and PartitionFactor=0.1:
Near the end of a month you will have (12 whole months)+(most of this month)+(an alllocation for coming month) eg ~13.1 months worth of storage.

Near the start of a month you will have (12 whole months)+(an alllocation for this month[partially used])+(an alllocation for nextmonth), ie. ~12.2 months worth of storage.

So assuming Partiition factor is less than 1 (and it should be in the 0.10 to 0.50 range [and it has a sensible default if unspecified]) the new formula looks like:

RequiredDiskSpace > MaxPartitionSize x (KeepDataMonths+1+PartitionFactor) + LOG size + Primary total size