Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/* Split upcoming partitions (work up to 3 months ahead of current month) */
DECLARE @nextPart DATE
, @curDate DATE = DATEADD(MONTH, 3, GETDATE()); -- we'll extend our partitions out to this date</code>
SELECT @nextPart = DATEADD(MONTH, 1, CAST(sprv.value AS DATETIME2))
FROM sys.partition_functions AS spf
INNER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_id
WHERE spf.name = N'pf_MonthlyWindow'
ORDER BY sprv.boundary_id DESC
OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY;
/* ensure empty partitions exist to the right to
prevent long processing times to split partition range */
WHILE ( @nextPart <= @curDate )
BEGIN
ALTER PARTITION SCHEME ps_MonthlyWindow NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_MonthlyWindow() SPLIT RANGE (@nextPart);
PRINT 'Added ' + CONVERT(VARCHAR(30), @nextPart, 120) + ' to pf_MonthlyWindow.'
SELECT @nextPart = DATEADD(MONTH, 1, @nextPart);
END;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment