Being on a business trip in Japan a customer described a case where they had their SAP system running on SQL Server. In their opinion they did everything right in terms of using our proportional fill features as it is described
. However after the last bigger archiving session where SD and FI objects of a whole year got archived, they saw that one file was emptied to a bigger degree than all the other files. Means they ended up with rather different free space portions in their files which were evenly sized. Using proportional fill with evenly sized files all the time this shouldn’t happen obviously. However the problem was in this specific case, that it the customer couldn’t tell whether the files of the SAP database always were managed in the most optimal way. Means it could have happened that years back when the data of the year which just got archived, got inserted one database file may have been larger or all the other database files might have been filled up already. There is no possibility to investigate whether this happened many years later. However let’s step through two short procedures to check whether the actual read and write activity is balanced between the data files and whether the data of a specific table is stored as well to even parts throughout all data files as we desire it to be for the way SAP databases should be created on SQL Server.
How do we check whether proportional fill works fine at the moment? Under the assumption that all data files have the same free space (even independent of the size of the data files), the following query batch would give us some indication whether proportional fill is working in even proportions over all data files: