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?
Solved! Go to Solution.
1. Is there a formula? Yes.
RequiredDiskSpace > MaxPartitionSize x (KeepDataMonths+1+(PartitionFactor x 2)) + LOG size + Primary total size
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.
Need to change the retention months? See this post for details:
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
Having looked at the planning phase in session one of this series, we will turn our focus to the readiness phase. The all important technical capabilities assessment, ensuring the network, endpoints and users are adequately prepared for the move.
Hear first hand from IR's Global Head of Information Systems and Technology, Jason Schwendinger, on how he has been tackling these issues.Join webinar