BW: 1000 partitions limit with SQL Server database
With SQL Server 2005 onwards, there is limitation of 1000 partitions for a table. In BW System, it is observed that customer can reach this limit often if not performing Compression of the InfoCubes and can result in system error.
Consequences of the reaching the 1000 partition limit:
Once 1000 partition limit has been reached in a SQL Server database, it will continue writing each new request to the 1000th partition and will write an error to the system log (SQL Error 7719 – see below snapshot). However, this was done only to avoid a hard failure when loading data, it is not the recommended business process that you keep loading requests to the last partition. Continuing to load to the 1000th partition could cause a performance problem later when trying to delete requests.
Also, if there are any aggregates on the InfoCube with 1000 partitions, next rollup on the aggregate will fail with a hard error giving DUMP on the system.
How to check number of partitions on the SQL server database:
Execute report RSDD_MSSQL_CUBEANALYZE
– Menu Settings => Expert mode
– Press the Details button
– Choose the number of minimum partitions
– Press the button Start Checks
For corrective action, you should follow SAP Note SAP Note 869407: “Partitioning on MS SQL Server 2005 and later releases”.
InfoCubes (F Fact table): Perform compression to reduce the number of partitions for each InfoCube to less than 800.
InfoCubes (F Fact table): Since the E fact table is partitioned by a characteristic, system will only allow to partition with a range of values that result in less than 1000 partitions.
Aggregate: To correct the aggregate, you need to first set the flag ‘Compress after rollup’ on the InfoCube management screen and then deactivate and reactivate the cube. When the reactivation and fill executes, it will also automatically compress the records to the E fact table of the aggregate so that the F fact table will not reach 1000 partitions.
PSA and DSO tables: It is usually not encountered to have 1000 partitions in PSA and DSO tables due to the differences in data loading procedures between InfoCubes and PSA/DSOs.