So this week there was this small fire around running out of partitions. Because I had to learn how all of it works really fast, I need to write it down before I forget. I was getting a range doesn’t exist error so here is what I did:
I already had my file groups created. If you don’t do that before you move on.
Find what partition schemes and functions you have in your database:
SELECT * FROM sys.partition_schemes SELECT * FROM sys.partition_functions
(Hint: the fanout is the last range so that can help you identify which one is failing.)
Next, see what your current ranges look like:
SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf INNER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_id WHERE (spf.name = N'PartitionFunctionName') ORDER BY [ID] ASC
Now we are going to split out our partitions one at a time. Even if you need to add 5 or more ranges, this is an easy step to do one at a time. Also, you can only split a range into to pieces at a time, so just run the whole query for each range you want to add.
ALTER PARTITION SCHEME [PartitionScheme] NEXT USED [YourFileGroup] ALTER PARTITION FUNCTION [PartitionFunction]() SPLIT RANGE (N'NewRange')
This should get you back on track.