They’ll never take my body from your side, Love don’t die from partitioning…

New dress for Christmas

New dress for Christmas

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.

Happy Trails!