Database Maintenance encounters an "CREATE/ALTER partition function failed as only a maximum of 15000 partitions can be created" error.

Run the following SQL query to check the number of partitions on the VolumeUsage_CS_Detail_hist table:

Execute the following SQL script to merge and remove empty, redundant partitions. Please note that this operation may take a considerable amount of time to complete.

​​​​

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

DECLARE @min_range DATE;
DECLARE @max_range DATE;
DECLARE @days INT;
DECLARE @part_function NVARCHAR(128);
DECLARE @sql NVARCHAR(1024);
DECLARE @table_name NVARCHAR(128) = 'VolumeUsage_CS_Detail_hist';
 
WITH parts
AS
(
SELECT OBJECT_NAME(p.object_id) AS table_name,
CAST(prv.value AS DATE) AS range_value,
pf.name,
p.rows
FROM sys.indexes ind WITH(NOLOCK)
LEFT HASH JOIN sys.partitions p WITH(NOLOCK)
ON ind.object_id = p.object_id
AND ind.index_id = p.index_id
LEFT HASH JOIN sys.allocation_units au WITH(NOLOCK)
ON au.container_id = p.hobt_id
LEFT HASH JOIN sys.filegroups fg WITH(NOLOCK)
ON fg.data_space_id = au.data_space_id
LEFT HASH JOIN sys.data_spaces ds WITH(NOLOCK)
ON ind.data_space_id = ds.data_space_id
LEFT HASH JOIN sys.partition_schemes sch WITH(NOLOCK)
ON sch.data_space_id = ds.data_space_id
LEFT HASH JOIN sys.partition_range_values prv WITH(NOLOCK)
ON prv.function_id = sch.function_id
AND prv.boundary_id = p.partition_number
LEFT HASH JOIN sys.partition_functions pf WITH(NOLOCK)
ON prv.function_id = pf.function_id
WHERE au.type = 1
AND p.object_id = OBJECT_ID(@table_name)
AND CAST(prv.value AS DATE) IS NOT NULL
AND p.rows = 0
)
SELECT @part_function = p.name,
@min_range = MIN(p.range_value),
@max_range = MAX(p.range_value)
FROM parts p
GROUP BY p.name
;
 
PRINT @min_range;
PRINT @max_range;
 
WHILE @max_range > @min_range
BEGIN
SET @sql = 'ALTER PARTITION FUNCTION ' + @part_function + '()' + ' MERGE RANGE (''' + (CAST(@min_range AS NVARCHAR(32))) + ''');';
--PRINT @sql;
EXEC sp_executesql @sql;
SET @min_range = DATEADD(DAY, 1, @min_range);
END;